ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL SERVER Merge的用法

SQL SERVER Merge的用法

原创 Linux操作系统 作者:释怀355 时间:2013-08-18 11:32:20 0 删除 编辑
Merge是在SQL Server 2008被引入,它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果,对目标表执行插入、更新或删除操作。例如,根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。”,通过这个描述,我们 可以看出Merge是关于对于两个表之间的数据进行操作的。

    可以想象出,需要使用Merge的场景比如:

  •     数据同步
  •     数据转换
  •     基于源表对目标表做Insert,Update,Delete操作

使用Merge关键字的好处

    首先是更加短小精悍的语句,在SQL Server 2008之前没有Merge的时代,基于源表对目标表进行操作需要分别写好几条Insert,Update,Delete。而使用Merge,仅仅需要使用一条语句就好。下面我们来看一个例子。

Merge的基本用法

    首先建立源表和目标表,并插入相关的数据。

CREATE TABLE DBO.SourceTable(
id INT,
[DESC] NVARCHAR(50)
)

CREATE TABLE DBO.TargetTable(
id  INT,
[DESC] NVARCHAR(50)
)

INSERT INTO dbo.SourceTable(id,[DESC])
SELECT 1,N'描述1'
UNION ALL
SELECT 2,N'描述2'
UNION ALL
SELECT 3,N'描述3'
UNION ALL
SELECT 4,N'描述4'
UNION ALL
SELECT 1,N'描述1'

INSERT INTO dbo.TargetTable(id,[DESC])
SELECT 1,N'在源表中存在,将会被更新'
UNION ALL
SELECT 2,N'在源表中存在,将会被更新'
UNION ALL
SELECT 5,N'在源表里不存在,将会被删除'
UNION ALL
SELECT 6,N'在源表里不存在,将会被删除'

下面我们来写一个简单的Merge语句。

MERGE INTO dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.ID=S.ID
WHEN MATCHED    --当上面On后面的T.id=S.id时,目标表中的数据被更新
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;

没有执行以上语句前源表与目标表中的数据如下:

源表

目标表

执行后:

源表

目标表

当然了,上面的Merge关键字后面使用了多个WHEN…THEN语句,而这个语句是可选的.也可以仅仅新增或是仅仅删除,

当然我们以下的测试都是将表中的数据恢复为初始插入的数据。

MERGE INTO dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.ID=S.ID
WHEN MATCHED
THEN UPDATE SET T.[DESC]=S.[DESC];

源表中的数据没有变化,对目标表仅仅进行了更新的数据如下图:

Merge 语句中的output 用法

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

MERGE INTO dbo.TargetTable AS T
USING dbo.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
OUTPUT $ACTION AS ACTION,
 INSERTED.ID AS 插入的Id
,INSERTED.[DESC] AS 插入的DESC
,DELETED.ID AS 删除的Id
,DELETED.[DESC] AS 删除的DESC;

此时Merge操作完成后,将所变动的语句进行输出,如下图:

                        输出Merge操作产生的数据变更

Merge语句中可以使用TOP限制

我们还可以使用TOP关键字限制目标表被操作的行,

MERGE TOP(4) dbo.TargetTable AS T
USING dbo.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
OUTPUT $ACTION AS ACTION,
 INSERTED.ID AS 插入的Id
,INSERTED.[DESC] AS 插入的DESC
,DELETED.ID AS 删除的Id
,DELETED.[DESC] AS 删除的DESC;

输出结果:

从输出结果我们可以看出,对目标表总共操作了四行,update 两行,insert 两行,如果不加TOP限制的话,这里目标表应该还会被删除掉两行。所以看来我们的TOP限制起了作用。

Matched中添加额外的限制条件

仅仅是MATCHED这种限制条件往往不能满足实际需求,我可以在以前语句中加上AND,附加上额外的限制条件

MERGE INTO dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.ID=S.ID
WHEN MATCHED AND S.ID=3 --加额外的限制条件
THEN UPDATE SET T.[DESC]=S.[DESC]
WHEN NOT MATCHED
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;

从输出结果看出,因为在目标表中没有满足update条件的数据,所以这里也没有update操作了。

Merge关键字的一些限制

  •     使用Merge关键字只能更新一个表
  •     源表中不能有重复的记录

对于后者的限制,我们可以做以下测试:

我们在源表中插入一条重复的数据:

源表中的数据

目标表中的数据

有两条id=1的数据。

然后执行以下merge语句:

MERGE INTO dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.ID=S.ID
WHEN MATCHED --AND S.ID=3 --加额外的限制条件
THEN UPDATE SET T.[DESC]=S.[DESC]
WHEN NOT MATCHED
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;

报错:

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

分析:对于update操作,源表有两行符合条件的,所以SQL SERVER肯定就不确定到底把哪行更新给目的表,所以就报错了。

如果我们加上限制条件AND S.ID=3来运行一下:

MERGE INTO dbo.TargetTable AS T
USING dbo.SourceTable AS S
ON T.ID=S.ID
WHEN MATCHED AND S.ID=3 --加额外的限制条件
THEN UPDATE SET T.[DESC]=S.[DESC]
WHEN NOT MATCHED
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;

没有报错,运行结果如下:

分析:即使源表中存在重复行,但是查询时并没有用到这两行,所以SQL SERVER 在执行时也不会出现源表有多行,无法跟目标表匹配的情况。

总结:所以对于源表不能存在重复行的限制不是绝对的,其实是指,在进行merge 更新表时不能出现源表有多行对应目标表的一行,出现无法一一对应的情况。

就像上面举的列子,源表存在了重复行,但是我在merge操作时并没有使用到该重复行,就不会有问题了。


文章来源 http://www.cnblogs.com/CareySon/archive/2012/03/07/2383690.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27026361/viewspace-768740/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-11

  • 博文量
    28
  • 访问量
    67224