【sqlserver】merge into test

--merge into test
/*
使用Merge关键字的好处：简洁有效
局限：在SQL Server 2008之前没有Merge
*/
create table testsou( --源测试表
id int,
name varchar(10),
des varchar(50)
)

create table testtag( --目标表
id int,
name varchar(10),
des varchar(50)
)

insert into testsou values(1,'small','a small dog'),(2,'big','a big cat'),(3,'middle','middle school');
insert into testtag values(1,'small','a small dog'),(2,'big','HEHE'),(4,'HEHE','');


merge into testtag t
using testsou s
on t.id=s.id and t.name=s.name
when matched --ON条件成立时更新
then update set t.des=s.des
when not matched --不成立时插入
then insert /*(字段信息)*/ values(s.id,s.name,s.des)
when not matched by source --目标表存在，源表不存在的情况（删除）
then delete;

select * from testsou;
select * from testtag;
--DATA SYNC testsou和testtag数据一致

drop table testsou;
drop table testtag;