Sql 2008的merge关键字

测试数据

/*目标表和源表*/
declare @target table(id int, de varchar(50))
declare @source table(id int, de varchar(50))
insert into @source values (1,'源1'),(2,'源2'),(3,'源3'),(4,'源4')
insert into @target values 
(1,'在源表里面有1'),(2,'在源表里面有2'),(5,'在源表里面没有5'),(6,'在源表里面没有6')
select * from @target

代码一

merge into @target as T
using @source as S on T.id=S.id
when matched then update set T.de=S.de
;
select * from @target

两张表通过id匹配. 如果符合. 则把目标表的de改为源表的de得到数据如. 红圈两列完成了更改

代码二.

merge into @target as T
using @source as S on T.id=S.id

when matched        then update set T.de=S.de
when not matched    then insert values(S.id,S.de)        --如果目标表里面没有源表的数据
/*when not matched by source
--                    then delete                            
--如果target里面有, 但是源表没有的就删除. 有点类似于inner join的笛卡尔乘积, 
--左边表里面存着记录, 但是右边表没有. 还是删除掉
*/
output $Action as actiontype,deleted.*,inserted.*
;
select * from @target