新萄京编程

 新萄京编程     |      2020-04-29

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

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

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

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

下面通过一个简单示例来演示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

以下SQL创建示例表:

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

--源表 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 列。本示例捕获已更新的行,并将这些行插入用于跟踪库存变化的另一个表中。

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

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

 

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

 

MERGE 目标表

 Notes

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

语句

WHEN NOT MATCHED THEN

语句;

以上是MERGE的最最基本的语法,语句执行时根据匹配条件的结果,如果在目标表中找到匹配记录则执行WHEN MATCHED THEN后面的语句,如果没有找到匹配记录则执行WHEN NOT MATCHED THEN后面的语句。注意源表可以是表,也可以是一个子查询语句。

格外强调一点,MERGE语句最后的分号是不能省略的!

回到我们的示例,显然Product与ProductNew表的MERGE匹配条件为主键ProductID字段,初始情况下,ProductNew表为空,此时肯定执行的是WHEN NOT MATCHED THEN后的语句,我们先只考虑源表递增的情况,MERGE语句如下: