在一些业务中,经常会用到对数据的比对,不存在插入,在源表中存在则更新,在源表不存在目标表存在则删除,一般都是会通过if else判断,
Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入,它能将Insert,Update,Delete简单的并为一句。
首先创建两张表,源表(SourceTable)和目标表(TargetTable)
其中SourceTable数据如下
TargetTable数据如下
merge进行数据操作
merge into TargetTable as T
using SourceTable as s
on T.id=S.id
when Matched
then update set T.note=s.note
when Not Matched
then insert values(s.id,s.note)
when not Matched by source
then delete;
结果如下:
Merge还有一个强大的功能是通过OUTPUT子句,可以将刚刚做过变动的数据进行输出
merge into TargetTable as T
using SourceTable as s
on T.id=S.id
when Matched
then update set T.note=s.note
when Not Matched
then insert values(s.id,s.note)
when not Matched by source
then delete
Output $Action as [Action],Inserted.id,
inserted.note,
deleted.id,
deleted.note;
操作结果如下:
当然,你也可以仅仅新增或是仅仅删除
merge into TargetTable as T
using SourceTable as s
on T.id=S.id
when Not Matched
then insert values(s.id,s.note);