SQLServer之merge函数用法

核心提示:就像标题呈现的一样,SQL Server
2008中的MERGE语句能做很多事情

根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。

同步操作

MERGE 目标表
USING 源表
ON 匹配条件
WHEN MATCHED THEN
语句
WHEN NOT MATCHED THEN
语句;

就像标题呈现的一样,SQL Server
2008中的MERGE语句能做很多事情,它的功能是根据源表对目标表执行插入、更新或删除操作。最典型的应用就是进行两个表的同步。

A. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作
下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单,每天更新
AdventureWorks 示例数据库中的 ProductInventory 表。通过减去每天对
SalesOrderDetail 表中的每种产品所下的订单数,更新 ProductInventory 表的
Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0
或更少,则会从 ProductInventory 表中删除该产品对应的行。

MERGE
INTO – 数据的目的地,将数据最终 MERGE 到的表对象

其中最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句

下面通过一个简单示例来演示MERGE语句的使用方法,假设数据库中有两个表Product及ProductNew,我们的任务是将Product的数据同步到ProductNew。

 
USE AdventureWorks;
GO
IF OBJECT_ID (N’Production.usp_UpdateInventory’, N’P’) IS NOT NULL
DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS
sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity – source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity –
source.OrderQty,
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity,
Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

USING
与源表连接 ON 关联的条件

WHEN NOT MATCHED BY TARGET
表示目标表不匹配,BY TARGET是默认的,所以上面我们直接使用WHEN NOT
MATCHED THEN
WHEN NOT MATCHED BY SOURCE
表示源表不匹配,即目标表中存在,源表中不存在的情况。

以下SQL创建示例表:

EXECUTE Production.usp_UpdateInventory ‘20030501’
B. 借助派生的源表,使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作
下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason
表。当源表中的 NewName 值与目标表 (SalesReason) 的 Name
列中的值匹配时,就会更新此目标表中的 ReasonType 列。当 NewName
的值不匹配时,就会将源行插入到目标表中。此源表是一个派生表,它使用
Transact-SQL
行构造函数功能指定源表的多个行。有关在派生表中使用行构造函数的详细信息,请参阅
FROM (Transact-SQL)。

WHEN
MATCHED – 如果匹配成功,即关联条件成功 (这时就应该将 SOURCE
中其它的所有字段值更新到 TARGET 表中)

主要用法:
merge无法多次更新同一行,也无法更新和删除同一行
当源表和目标表不匹配时:
若数据是源表有目标表没有,则进行插入操作
若数据是源表没有而目标表有,则进行更新或者删除数据操作
当源表和目标表匹配时:
进行更新操作或者删除操作

–源表 CREATE TABLE Product ( ProductID varchar(7) NOT NULL PRIMARY KEY,
ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

 
USE AdventureWorks;
GO
MERGE INTO Sales.SalesReason AS Target
USING (VALUES (‘Recommendation’,’Other’), (‘Review’, ‘Marketing’),
(‘Internet’, ‘Promotion’))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
 UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
 INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action, inserted.*, deleted.*;
C. 将 MERGE 语句的执行结果插入到另一个表中
下例捕获从 MERGE 语句的 OUTPUT
子句返回的数据,并将该数据插入另一个表。MERGE 语句根据在
SalesOrderDetail 表中处理的订单,更新 ProductInventory 表的 Quantity
列。本示例捕获已更新的行,并将这些行插入用于跟踪库存变化的另一个表中。

WHEN
NOTMATCHED BY TARGET – 如果匹配不成功 (TARGET 中没有这一条记录但是
SOURCE 表有,说明 SOURCE 表多了新数据因此应该插入到 TARGET 表中)

when matched 这个子句可以有两个,当有两个时,第一个子句必须是when
matched and condition且两个matched子句只会执行一个,且两个子句必须
是一个update和一个delete操作
when not matched by source和上面类似

INSERT INTO Product Values (4100037,优盘,50), (4100038,鼠标,30);

 
USE AdventureWorks;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty
int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID,
LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN ‘20030701’ AND ‘20030731’
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity – src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity – src.OrderQty
    WHEN MATCHED AND pi.Quantity – src.OrderQty <= 0
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID,
Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE
Action = ‘UPDATE’;
GO

WHEN
NOTMATCHED BY SOURCE – 如果匹配不成功 (SOURCE 中没有这一条记录但是
TARGET 表有,说明 SOURCE 表可能把这条数据删除了,所以 TARGET
也应该删除)

merge icr_codemap_bak as a
using icr_codemap as b
on a.COLNAME = b.COLNAME and a.ctcode = b.ctcode
when matched and b.pbcode <> a.pbcode
then update set a.pbcode = b.pbcode
when not matched
then insert values(b.colname,b.ctcode,b.pbcode,b.note)
;
可以比对字段不一致进行更新

这个是MSDN的网址

–目标表 CREATE TABLE ProductNew ( ProductID varchar(7) NOT NULL PRIMARY
KEY, ProductName varchar(100) NOT NULL, Price decimal(13,2) DEFAULT 0 );

 

MERGE INTO @TargetTable AS T           
USING @SourceTable AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.DSPT = S.DSPT WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(S.ID,S.DSPT) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $ACTION AS [ACTION],
   Deleted.ID AS 'Deleted ID',
   Deleted.DSPT AS 'Deleted Description',
   Inserted.ID AS 'Inserted ID',
   Inserted.DSPT AS 'Inserted Description' INTO @Log;

在 Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE 语句。
在 Merge Not Matched 操作中,只允许执行 INSERT 语句。
一个 Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE
语句,否则就会出现下面的错误 – An action of type ‘WHEN MATCHED’ cannot
appear more than once in a ‘UPDATE’ clause of a MERGE statement.
Merge 语句最后必须包含分号,以 ; 结束。

下面再来关注MERGE语句的基本语法:

 

还要注意的是有一些限制条件:

 

MERGE 目标表

 Notes


  • Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE
    语句。

  • Merge Not Matched 操作中,只允许执行 INSERT 语句。
  • 一个
    Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE
    语句,否则就会出现下面的错误 – An action of type ‘WHEN MATCHED’ cannot
    appear more than once in a ‘UPDATE’ clause of a MERGE
    statement.
  • Merge
    语句最后必须包含分号,以 ; 结束。

USING 源表

  • The MERGE SQL statement requires a semicolon (;) as a statement
    terminator. Otherwise Error 10713 is raised when a MERGE statement
    is executed without the statement terminator.
  • When used after MERGE, @@ROWCOUNT returns the total number of rows
    inserted, updated, and deleted to the client.
  • At least one of the three MATCHED clauses must be specified when
    using MERGE statement; the MATCHED clauses can be specified in any
    order. However a variable cannot be updated more than once in the
    same MATCHED clause.
  • Of course it’s obvious, but just to mention, the person executing
    the MERGE statement should have SELECT Permission on the SOURCE
    Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
  • MERGE SQL statement improves the performance as all the data is read
    and processed only once whereas in previous versions three different
    statements have to be written to process three different activities
    (INSERT, UPDATE or DELETE) in which case the data in both the source
    and target tables are evaluated and processed multiple times; at
    least once for each statement.
  • MERGE SQL statement takes same kind of locks minus one Intent Shared
    (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as
    we did in previous version of SQL Server.
  • For every insert, update, or delete action specified in the MERGE
    statement, SQL Server fires any corresponding AFTER triggers defined
    on the target table, but does not guarantee on which action to fire
    triggers first or last. Triggers defined for the same action honor
    the order you specify.

ON 匹配条件

 

例子:

WHEN MATCHED THEN

–源表

发表评论

电子邮件地址不会被公开。 必填项已用*标注