SQL Server中的Merge关键字(转载)
简介
Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入,它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果,对目标表执行插入、更新或删除操作。例如,根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。”,通过这个描述,我们可以看出Merge是关于对于两个表之间的数据进行操作的。
可以想象出,需要使用Merge的场景比如:
- 数据同步
- 数据转换
- 基于源表对目标表做Insert,Update,Delete操作
使用Merge关键字的好处
首先是更加短小精悍的语句,在SQL Server 2008之前没有Merge的时代,基于源表对目标表进行操作需要分别写好几条Insert,Update,Delete。而使用Merge,仅仅需要使用一条语句就好。下面我们来看一个例子。
首先建立源表和目标表,并插入相关的数据,如图1所示。
图1.创建测试表并插入测试数据
下面把图1的脚本用文本贴出来。
--创建源表 CREATE TABLE [dbo].[SourceTable]( [id] int, [DESC] nvarchar(50) ) --创建目标表 CREATE TABLE [dbo].[TargetTable]( [id] int, [DESC] nvarchar(50) ) --为源表插入数据 INSERT INTO [dbo].[SourceTable] VALUES(1,'描述1') INSERT INTO [dbo].[SourceTable] VALUES(2,'描述2') INSERT INTO [dbo].[SourceTable] VALUES(3,'描述3') INSERT INTO [dbo].[SourceTable] VALUES(4,'描述4') --为目标表插入数据 INSERT INTO [dbo].[TargetTable] VALUES(1,'在源表里存在,将会被更新') INSERT INTO [dbo].[TargetTable] VALUES(2,'在源表里存在,将会被更新') INSERT INTO [dbo].[TargetTable] VALUES(5,'在源表里不存在,将会被删除') INSERT INTO [dbo].[TargetTable] VALUES(6,'在源表里不存在,将会被删除')
下面我们来写一个简单的Merge语句,如图2所示。
图2.一个简单的Merge语句,注意上面USING关键字后面的SourceTable也可以是一个子查询,例如:
MERGE INTO TargetTable AS T USING (SELECT id, [DESC] FROM SourceTable) AS S ON T.id=S.id WHEN MATCHED THEN UPDATE SET T.[DESC]=S.[DESC] WHEN NOT MATCHED THEN INSERT VALUES(S.id, S.[DESC]) WHEN NOT MATCHED BY SOURCE THEN DELETE;
所造成的结果如图3所示。
图3.Merge语句对于目标表的更新
最终目标表的结果如图4所示。
图4.最后目标表的结果
我们还可以在用Merge语句插入(INSERT)数据到TargetTable时,只插入部分列,这在TargetTable中有自增列或默认值列时非常有用,如下所示:
MERGE INTO TargetTable AS T USING SourceTable AS S ON T.id=S.id WHEN MATCHED THEN UPDATE SET T.[DESC]=S.[DESC] WHEN NOT MATCHED THEN INSERT([DESC]) VALUES(S.[DESC]) --只插入DESC列到TargetTable,不插入id列 WHEN NOT MATCHED BY SOURCE THEN DELETE;
目标表的结果如图5所示。
图5.目标表中只插入了列DESC
Merge语句还有一个强大的功能是通过OUTPUT子句,可以将刚刚做过变动的数据进行输出。我们在上面的Merge语句后加入OUTPUT子句,如图6所示。
图6.Merge语句后加上OUTPUT子句
此时Merge操作完成后,将所变动的语句进行输出,如图7所示。
图7.输出Merge操作产生的数据变更
我们还可以将Merge语句OUTPUT关键字的输出结果存放在另一张表里,供后续使用,如下所示。
CREATE TABLE #MergeResult --定义一个临时表#MergeResult来存放下面MERGE语句OUTPUT关键字的输出结果 ( [结果表_id] INT IDENTITY(1,1), [结果表_Action] NVARCHAR(50), [结果表_插入的id] NVARCHAR(50), [结果表_插入的DESC] NVARCHAR(50), [结果表_删除的id] NVARCHAR(50), [结果表_删除的DESC] NVARCHAR(50) ); MERGE INTO TargetTable AS T USING SourceTable AS S ON T.id=S.id WHEN MATCHED --当上面on后面的T.id=S.id时,目标表中的id为1,2的数据被更新 THEN UPDATE SET T.[DESC]=S.[DESC] WHEN NOT MATCHED --目标表中没有的id,在源表中有,则插入相关数据 THEN INSERT VALUES(S.id, S.[DESC]) WHEN NOT MATCHED BY SOURCE --目标表中存在,源表中不存在,则删除 THEN DELETE OUTPUT $ACTION AS [ACTION], Inserted.id AS 插入的id, Inserted.[DESC] AS 插入的DESC, Deleted.id AS 删除的id, Deleted.[DESC] AS 删除的DESC INTO #MergeResult([结果表_Action],[结果表_插入的id],[结果表_插入的DESC],[结果表_删除的id],[结果表_删除的DESC]); --将OUTPUT关键字后的输出结果插入表#MergeResult SELECT * FROM #MergeResult; --查询表#MergeResult
最后临时表#MergeResult的结果如图8所示。
图8.临时表#MergeResult的结果
当然了,上面的Merge关键字后面使用了多个WHEN…THEN语句,而这个语句是可选的.也可以仅仅新增或是仅仅删除,如图9所示。
图9.仅仅插入的Merge语句
我们还可以使用TOP关键字限制目标表被操作的行,如图10所示。在图2的语句基础上加上了TOP关键字,我们看到只有两行被更新。
图10.使用TOP关键字的Merge语句
但仅仅是MATCHED这种限制条件往往不能满足实际需求,我们可以在图9那个语句的基础上加上AND附加上额外的限制条件,如图11所示。
图11.加上了AND限制条件的Merge语句
Merge关键字的一些限制
- 使用Merge关键字只能更新一个表
- 源表中不能有重复的记录
小结
本文简单说明了Merge关键字的使用。如果你使用的是SQL Server 2008之后的版本,在面对一些比如库存结账之类的业务时,放弃IF…ELSE和手写UPDATE,Insert吧,使用Merge关键字可以使这类操作更加轻松愉悦。