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所示。

SQL Server中的Merge关键字（转载）

图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所示。

SQL Server中的Merge关键字（转载）

图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所示。

SQL Server中的Merge关键字（转载）

图3.Merge语句对于目标表的更新

最终目标表的结果如图4所示。

SQL Server中的Merge关键字（转载）

图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所示。

SQL Server中的Merge关键字（转载）

　图5.目标表中只插入了列DESC

Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出。我们在上面的Merge语句后加入OUTPUT子句,如图6所示。

SQL Server中的Merge关键字（转载）

图6.Merge语句后加上OUTPUT子句

此时Merge操作完成后，将所变动的语句进行输出,如图7所示。

SQL Server中的Merge关键字（转载）

图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