select 1 from _dept t where t.smt_deptcode=d.smt_deptcode and not (
t.smt_deptno=d.smt_deptno and
t.smt_deptname=d.smt_deptname and
t.smt_level=d.smt_level and
t.smt_least=d.smt_least and
t.smt_explain=d.smt_explain and
t.smt_parent_id=d.smt_parent_id and
t.smt_dailycode=d.smt_dailycode and
t.smt_dailyno=d.smt_dailyno and
t.smt_shortname=d.smt_shortname and
t.smt_enlishname=d.smt_enlishname and
t.smt_mnemonic=d.smt_mnemonic and
t.smt_codeby=d.smt_codeby and
t.smt_gbflag=d.smt_gbflag and
t.smt_index=d.smt_index
)
);
--删除
delete from _dept@door d where not exists(
select 1 from _dept s where d.smt_deptcode=s.smt_deptcode
);
commit;
--***********************人事信息同步 *******************----
--插入操作（没有的数据）
insert into _personnel@door (
smt_personnelid,
smt_name,
smt_namespell,
smt_sex,
smt_birthday,
smt_salaryno,
smt_idcode,
smt_idno,
smt_datasource,
smt_indatetime,
smt_email,
smt_deptcode,
smt_postcode,
smt_statuscode,
smt_dossiercode,
smt_native,
smt_people,
smt_married,
smt_dwelling,
smt_polity,
smt_phoneno,
smt_dailycode,
smt_dailyno,
smt_postalcode,
smt_linkaddress,
smt_mobiletelephone,
smt_linkman,
smt_linkunit,
smt_linkmanaddress,
smt_linkmanphone,
smt_linkmanmobile,
smt_levelcode,
smt_countrycode,
smt_idcardno,
smt_visadate,
smt_validitydate,
smt_visaorgan,
smt_specialtycode,
smt_knowledgecode,
smt_incompanydate,
smt_salarycode,
smt_specialtypostcode,
smt_degreecode
)
select * from _personnel s where not exists(
select 1 from _personnel@door d where d.smt_personnelid=s.smt_personnelid
);
--更新(更新改变的数据)
update _personnel@door d set
(
smt_name,
smt_namespell,
smt_sex,
smt_birthday,
smt_salaryno,
smt_idcode,
smt_idno,
smt_datasource,
smt_email,
smt_deptcode,
smt_postcode,
smt_statuscode,
smt_dossiercode,
smt_native,
smt_people,
smt_married,
smt_dwelling,
smt_polity,
smt_phoneno,
smt_dailycode,
smt_dailyno,
smt_postalcode,
smt_linkaddress,
smt_mobiletelephone,
smt_linkman,
smt_linkunit,
smt_linkmanaddress,
smt_linkmanphone,
smt_linkmanmobile,
smt_levelcode,
smt_countrycode,
smt_idcardno,
smt_visadate,
smt_validitydate,
smt_visaorgan,
smt_specialtycode,
smt_knowledgecode,
smt_incompanydate,
smt_salarycode,
smt_specialtypostcode,
smt_degreecode
)=
(select
smt_name,
smt_namespell,
smt_sex,
smt_birthday,
smt_salaryno,
smt_idcode,
smt_idno,
smt_datasource,
smt_indatetime,
smt_email,
smt_deptcode,
smt_postcode,
smt_statuscode,
smt_dossiercode,
smt_native,
smt_people,
smt_married,
smt_dwelling,
smt_polity,
smt_phoneno,
smt_dailycode,
smt_dailyno,
smt_postalcode,
smt_linkaddress,
smt_mobiletelephone,
smt_linkman,
smt_linkunit,
smt_linkmanaddress,
smt_linkmanphone,
smt_linkmanmobile,
smt_levelcode,
smt_countrycode,
smt_idcardno,
smt_visadate,
smt_validitydate,
smt_visaorgan,
smt_specialtycode,
smt_knowledgecode,
smt_incompanydate,
smt_salarycode,
smt_specialtypostcode,
smt_degreecode
from _personnel s where s.smt_personnelid=d.smt_personnelid)
where exists
(select 1 from _personnel t where t.smt_personnelid=d.smt_personnelid and not (
t.smt_nam