merge合并源表记录到目标表

--合并源表记录到目标表
--合并规则：根据主键字段判断 存在则更新，不存在则插入，源表中记录已不存在则删除目标表中相应记录

--源表
if object_id('customer') is not null
drop table customer
go
create 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 all
select 2,'lei hu','intel'
union all
select 3,'jun wen li','hp'
union all
select 4,'jin hao liu','ibm'
go


--目标表
if object_id('customerstage') is not null
drop table customerstage
go
create 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 all
select 3,'jun wen li','alibaba'
union all
select 4,'jin hao liu','siemens'
union all
select 5,'cheng fan','bank'
go


--Customer为目标表,CustomerStage为源表,将源表的记录合并到目标表,当cusid匹配时,更新Customer中的信息,否则插入
merge into customer as tgt
using 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 tgt
using 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;