t.smt_namespell = d.smt_namespell and
t.smt_sex =d.smt_sex and
t.smt_birthday =d.smt_birthday and
t.smt_salaryno =d.smt_salaryno and
t.smt_idcode =d.smt_idcode and
t.smt_idno =d.smt_idno and
t.smt_datasource =d.smt_datasource and
t.smt_indatetime =d.smt_indatetime and
t.smt_email =d.smt_email and
t.smt_deptcode =d.smt_deptcode and
t.smt_postcode =d.smt_postcode and
t.smt_statuscode =d.smt_statuscode and
t.smt_dossiercode =d.smt_dossiercode and
t.smt_native =d.smt_native and
t.smt_people =d.smt_people and
t.smt_married =d.smt_married and
t.smt_dwelling =d.smt_dwelling and
t.smt_polity =d.smt_polity and
t.smt_phoneno =d.smt_phoneno and
t.smt_dailycode =d.smt_dailycode and
t.smt_dailyno =d.smt_dailyno and
t.smt_postalcode =d.smt_postalcode and
t.smt_linkaddress =d.smt_linkaddress and
t.smt_mobiletelephone =d.smt_mobiletelephone and
t.smt_linkman =d.smt_linkman and
t.smt_linkunit =d.smt_linkunit and
t.smt_linkmanaddress =d.smt_linkmanaddress and
t.smt_linkmanphone =d.smt_linkmanphone and
t.smt_linkmanmobile =d.smt_linkmanmobile and
t.smt_levelcode =d.smt_levelcode and
t.smt_countrycode =d.smt_countrycode and
t.smt_idcardno =d.smt_idcardno and
t.smt_visadate =d.smt_visadate and
t.smt_validitydate =d.smt_validitydate and
t.smt_visaorgan =d.smt_visaorgan and
t.smt_specialtycode =d.smt_specialtycode and
t.smt_knowledgecode =d.smt_knowledgecode and
t.smt_incompanydate =d.smt_incompanydate and
t.smt_salarycode =d.smt_salarycode and
t.smt_specialtypostcode =d.smt_specialtypostcode and
t.smt_degreecode =d.smt_degreecode
)
);
--删除操作
delete from _personnel@door d where not exists(
select 1 from _personnel s where d.smt_personnelid=s.smt_personnelid
);
commit;
-- *********************卡信息同步 ******************* ----
insert into _card@door
select * from _card s where not exists(
select 1 from _card@door d where d.smt_cardid=s.smt_cardid
);
--更新改变的数据
update _card@door d set (
smt_cardid,
smt_personnelid,
smt_cardno,
smt_showcardno,
smt_cardcode,
smt_bankcardcode,
smt_cardserial,
smt_accounts,
smt_bankbakno,
smt_carddate,
smt_startcode,
smt_startdate,
smt_validitydate,
smt_endcode,
smt_enddate,
smt_changedate,
smt_password,
smt_org_id,
smt_operatorid,
smt_dailycode,
smt_dailyno,
smt_deposit,
smt_returndeposit,
smt_depreciation,
smt_blacklistinvoiceno,
smt_oldcardid )=
(select
smt_cardid,
smt_personnelid,
smt_cardno,
smt_showcardno,
smt_cardcode,
smt_iccardcode,
smt_bankcardcode,
smt_cardserial,
smt_accounts,
smt_bankbakno,
smt_carddate,
smt_startcode,
smt_startdate,
smt_validitydate,
smt_endcode,
smt_enddate,
smt_changedate,
smt_password,
smt_org_id,
smt_operatorid,
smt_dailycode,
smt_dailyno,
smt_deposit,
smt_returndeposit,
smt_depreciation,
smt_blacklistinvoiceno,
smt_oldcardid
from _card where smt_cardid=d.smt_cardid)
where exists(
select 1 from _card s where s.smt_cardid=d.smt_cardid and
not (
s.smt_personnelid = d.smt_personnelid and
s.smt_cardno = d.smt_cardno and
s.smt_showcardno = d.smt_showcardno and
s.smt_cardcode = d.smt_cardcode and
s.smt_iccardcode = d.smt_iccardcode and
s.s