--合并源表记录到目标表--合并规则：根据主键字段判断 存在则更新，不存在则插入，源表中记录已不存在则删除目标表中相应记录--源表if object_id('customer') is not nulldrop table customergocreate table customer( cusid int not null, customename varchar(20), companyname varchar(20), constraint pk_cusid primary key (cusid))insert into customer(cusid,customename,companyname)select 1,'guo hu','wicresoft'union allselect 2,'lei hu','intel'union allselect 3,'jun wen li','hp'union allselect 4,'jin hao liu','ibm'go--目标表if object_id('customerstage') is not nulldrop table customerstagegocreate table customerstage( cusid int not null, customename varchar(20), companyname varchar(20), constraint pk_cusstageid primary key (cusid))insert into customerstage(cusid,customename,companyname)select 1,'guo hu','microsoft'union allselect 3,'jun wen li','alibaba'union allselect 4,'jin hao liu','siemens'union allselect 5,'cheng fan','bank'go--Customer为目标表,CustomerStage为源表,将源表的记录合并到目标表,当cusid匹配时,更新Customer中的信息,否则插入merge into customer as tgtusing customerstage as scr on tgt.cusid=scr.cusid when matched then update set tgt.customename=scr.customename, tgt.companyname=scr.companyname when not matched then insert(cusid,customename,companyname) values(scr.cusid,scr.customename,scr.companyname);--Customer为目标表,CustomerStage为源表,将源表的记录合并到目标表,当cusid匹配时,更新Customer中的信息,否则插入,当记录在源表中已不存在时删除merge into customer as tgtusing customerstage as src on tgt.cusid=src.cusid when matched then update set tgt.customename=src.customename, tgt.companyname=src.companyname when not matched then insert(cusid,customename,companyname) values(src.cusid,src.customename,src.companyname) when not matched by source then delete;
