• 项目

使用 MERGE 插入、更新和删除数据

在 SQL Server 2008 中，您可以使用 MERGE 语句在一条语句中执行插入、更新或删除操作。MERGE 语句允许您将数据源与目标表或视图联接，然后根据该联接的结果对目标执行多项操作。例如，您可以使用 MERGE 语句执行以下操作：

  • 有条件地在目标表中插入或更新行。

    如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。

  • 同步两个表。

    根据与源数据的差别在目标表中插入、更新或删除行。

MERGE 语法包括五个主要子句：

  • MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。

  • USING 子句用于指定要与目标联接的数据源。

  • ON 子句用于指定决定目标与源的匹配位置的联接条件。

  • WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。

  • OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。

有关语法和规则的完整详细信息，请参阅 MERGE (Transact-SQL)。

指定源搜索条件和目标搜索条件

应当了解源数据和目标数据是如何合并到单个输入流中的以及如何使用其他搜索条件正确筛选出不需要的行，这一点十分重要。否则，您指定的其他搜索条件可能会产生不正确的结果。

源中的行基于在 ON 子句中指定的联接谓词与目标中的行进行匹配。结果是合并后的输入流。对于每个输入行，会执行一个插入、更新或删除操作。根据在语句中指定的 WHEN 子句，输入行可能是以下内容之一：

  • 由来自目标的一个行和来自源的一个行组成的一个匹配对。这是 WHEN MATCHED 子句的结果。

  • 来自源的一个行，在目标中没有与之对应的行。这是 WHEN NOT MATCHED BY TARGET 子句的结果。

  • 来自目标的一个行，在源中没有与之对应的行。这是 WHEN NOT MATCHED BY SOURCE 子句的结果。

在 MERGE 语句中指定的 WHEN 子句的组合决定了由查询处理器实现并影响最终输入流的联接类型。以下源表和目标表示例及数据对此进行了阐释。

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

下表列出了可能的联接类型，并且指示了查询优化器实现各类型的条件。此外，该表还显示了在用于匹配源数据和目标数据的搜索条件为 Source.EmployeeID = Target.EmployeeID 时，示例源表和目标表所产生的输入流。

联接类型

实现

示例输入流结果

INNER JOIN

唯一指定的 WHEN 子句是 WHEN MATCHED 子句。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------

NULL     NULL    NULL     NULL

LEFT OUTER JOIN

指定了 WHEN NOT MATCHED BY TARGET 子句，但未指定 WHEN NOT MATCHED BY SOURCE 子句。可能指定了 WHEN MATCHED，也可能未指定。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------100      Mary    NULL     NULL

101      Sara    NULL     NULL

102      Stefano NULL     NULL

RIGHT OUTER JOIN

指定了 WHEN MATCHED 子句和 WHEN NOT MATCHED BY SOURCE 子句，但未指定 WHEN NOT MATCHED BY TARGET 子句。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------NULL     NULL    103      Bob

NULL     NULL    104      Steve

FULL OUTER JOIN

指定了 WHEN NOT MATCHED BY TARGET 子句和 WHEN NOT MATCHED BY SOURCE 子句。可能指定了 WHEN MATCHED，也可能未指定。

SrcEmpID SrcName TrgEmpID TrgName

-------- ------- -------- -------100      Mary    NULL     NULL

101      Sara    NULL     NULL

102      Stefano NULL     NULL

NULL     NULL    103      Bob

NULL     NULL    104      Steve

ANTI SEMI JOIN

唯一指定的 WHEN 子句是 WHEN NOT MATCHED BY SOURCE 子句。

TrgEmpID TrgName

-------- -------

100      Mary

101      Sara

102      Stefano

通过示例输入流结果可以看出，输入流结果取决于 WHEN 子句的组合。现在，假定要基于该输入流对目标表执行以下操作：

  • 雇员 ID 在目标表中不存在并且源雇员名称以“S”开头时，插入源表中的行。

  • 目标雇员名称以“S”开头并且雇员 ID 在源表中不存在时，删除目标表中的行。

若要执行这些操作，以下 WHEN 子句是必需的：

  • WHEN NOT MATCHED BY TARGET THEN INSERT

  • WHEN NOT MATCHED BY SOURCE THEN DELETE

如上表所述，指定这两个 WHEN NOT MATCHED 子句后，最终的输入流将会是源表和目标表的完整外部联接。现在已知了输入流结果，请考虑如何对输入流应用插入、更新和删除操作。

如前所述，WHEN 子句基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定了所要采取的操作。在很多情况下，在 ON 子句中指定的搜索条件可产生所需的输入流。但在示例方案中，插入和删除操作需要执行其他筛选以将受影响的行限制为具有以“S”开头的雇员名称的行。在以下示例中，对 WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE 应用了筛选条件。语句的输出表明，期望从输入流中得到的行已被纠正、插入或删除。

-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

以下是 OUTPUT 子句的结果。

$action   EmployeeID  EmployeeName EmployeeID  EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE    NULL        NULL         101         Sara

DELETE    NULL        NULL         102         Stefano

INSERT    104         Steve        NULL        NULL

(3 row(s) affected)

通过对 ON 子句指定附加搜索条件（例如，指定 ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%'）以在处理过程的早期阶段减少输入流中的行数，可能会提高查询性能。但这样做可能导致意外结果和不正确的结果。因为在 ON 子句中指定的附加搜索条件不用于匹配源数据和目标数据，它们可能会被误用。

下面的示例阐释错误结果是如何产生的。在 ON 子句中同时指定了用于匹配源表和目标表的搜索条件以及用于筛选行的附加搜索条件。由于附加搜索条件对于确定源和目标匹配来说不是必需的，因此插入和删除操作将应用于所有输入行。实际上，筛选条件 EmployeeName LIKE 'S%' 将被忽略。语句运行后，inserted 和 deleted 表的输出表明有两行被错误地进行了修改：从目标表中错误地删除了 Mary，同时错误地插入了 Bob。

-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%' 
    AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO

以下是 OUTPUT 子句的结果。

$action   EmployeeID  EmployeeName EmployeeID  EmployeeName

--------- ----------- ------------ ----------- ------------

DELETE    NULL        NULL         100         Mary

DELETE    NULL        NULL         101         Sara

DELETE    NULL        NULL         102         Stefano

INSERT    103         Bob          NULL        NULL

INSERT    104         Steve        NULL        NULL

(5 row(s) affected)

搜索条件指导原则

必须正确指定用于匹配源行和目标行的搜索条件和用于从源或目标中筛选行的其他搜索条件，以确保获得正确结果。建议遵循以下指导原则：

  • 在 ON <merge_search_condition> 子句中仅指定这样的搜索条件：这些搜索条件决定源表与目标表中数据的匹配标准。也就是说，仅指定与源表中的对应列进行比较的目标表列。

  • 不要包括与其他值（如常量）的比较。

若要从源表或目标表筛选出行，请使用以下方法之一：

  • 在适当的 WHEN 子句中指定用于行筛选的搜索条件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...。

  • 对返回筛选行的源表或目标表定义视图，并且将该视图作为源表或目标表进行引用。如果该视图是针对目标表定义的，则针对该视图的任何操作都必须满足更新视图所需的条件。有关使用视图更新数据的详细信息，请参阅通过视图修改数据。

  • 使用 WITH <通用表表达式> 子句从源表或目标表中筛选出行。此方法类似于在 ON 子句中指定附加搜索条件，并可能产生不正确的结果。建议您避免使用此方法，或者在采用它前进行全面测试。

示例

A. 使用简单的 MERGE 语句执行 INSERT 和 UPDATE 操作

假定您在数据仓库数据库中有一个 FactBuyingHabits 表，该表用于跟踪每个客户购买特定产品的最后日期。OLTP 数据库中的第二个表 Purchases 用于记录给定周的购买情况。您每周都要从 Purchases 表向 FactBuyingHabits 表中添加特定客户以前从未购买过的产品的行。对于购买以前曾经购买过的产品的客户的行，您只需更新 FactBuyingHabits 表中的购买日期即可。可以使用 MERGE 在一条语句中执行这些插入和更新操作。

下例首先创建 Purchases 和 FactBuyingHabits 表，然后使用某些示例数据加载这些表。当对联接键创建了 UNIQUE 索引时，MERGE 语句的性能也会提高，因而通过对这两个表中 ProductID 列创建 PRIMARY KEY 约束会创建聚集索引。

在本示例中，Purchases 包含 2006 年 8 月 21 日所在的这周的购买情况。FactBuyingHabits 包含相应上一周的购买情况，通常该表会填充有可追溯到更早些时候的行。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL 
    DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
    ProductID int, CustomerID int, PurchaseDate datetime, 
    CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL 
    DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
    ProductID int, CustomerID int, LastPurchaseDate datetime, 
    CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO

这些表现在填充了下列数据：

dbo.Purchases

ProductID CustomerID PurchaseDate

----------- ----------- -----------------------

707         11794       2006-08-20 00:00:00.000

707         15160       2006-08-25 00:00:00.000

708         18529       2006-08-21 00:00:00.000

711         11794       2006-08-20 00:00:00.000

711         19585       2006-08-22 00:00:00.000

712         14680       2006-08-26 00:00:00.000

712         21524       2006-08-26 00:00:00.000

712         19072       2006-08-20 00:00:00.000

870         15160       2006-08-23 00:00:00.000

870         11927       2006-08-24 00:00:00.000

870         18749       2006-08-25 00:00:00.000

dbo.FactBuyingHabits

ProductID CustomerID LastPurchaseDate

----------- ----------- -----------------------

707         11794       2006-08-14 00:00:00.000

707         18178       2006-08-18 00:00:00.000

864         14114       2006-08-18 00:00:00.000

866         13350       2006-08-18 00:00:00.000

866         20201       2006-08-15 00:00:00.000

867         20201       2006-08-14 00:00:00.000

869         19893       2006-08-15 00:00:00.000

870         17151       2006-08-18 00:00:00.000

870         15160       2006-08-17 00:00:00.000

871         21717       2006-08-17 00:00:00.000

871         21163       2006-08-15 00:00:00.000

871         13350       2006-08-15 00:00:00.000

873         23381       2006-08-15 00:00:00.000

请注意，这两个表中有两个共有的产品-客户行：分别于本周和上周，客户 11794 购买了产品 707，客户 15160 购买了产品 870。对于这些行，我们可以使用 WHEN MATCHED THEN 子句利用 Purchases 中这些购买记录的日期来更新 FactBuyingHabits。我们可以使用 WHEN NOT MATCHED THEN 子句将所有其他行插入 FactBuyingHabits。

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

B. 执行 UPDATE 和 DELETE 操作

以下示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新 AdventureWorks2008R2 示例数据库中的 ProductInventory 表。使用以下 MERGE 语句后，ProductInventory 表的 Quantity 列将通过减去每天为每种产品所下订单数的方式进行更新。如果某种产品的订单数导致该产品的库存下降为 0 或 0 以下，则会从 ProductInventory 表中删除该产品的行。请注意，源表已经聚合到 ProductID 列。如果不这样做，源表中会有多个 ProductID 与目标表匹配，从而导致 MERGE 语句返回错误。

USE AdventureWorks2008R2;
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

EXECUTE Production.usp_UpdateInventory '20030501'

C. 执行 INSERT、UPDATE 和 DELETE 操作

下例使用 MERGE 基于与源数据的差别在目标表中插入、更新或删除行。假设有一个小公司，该公司有五个部门，每个部门有一位部门经理。该公司决定对这些部门进行重组。若要在目标表 dbo.Departments 中实现重组结果，MERGE 语句必须实现以下更改：

  • 现有的一些部门将不会变化。

  • 现有的一些部门将任命新的经理。

  • 将会新建一些部门。

  • 一些部门在重组后将不再存在。

下面的代码将创建目标表 dbo.Departments，并在表中填充相应的经理。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments 
    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'), 
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer');

对部门所做的组织更改存储在源表 dbo.Departments_delta 中。以下代码创建并填充了此表：

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL 
    DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30), 
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES 
    (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
    (3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'), 
    (6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO

最后，为在目标表中反映公司重组，以下代码使用 MERGE 语句将源表 dbo.Departments_delta 与目标表 dbo.Departments 进行比较。此比较的搜索条件在该语句的 ON 子句中定义。根据比较的结果，将执行以下操作。

  • 在表 Departments 中，在源表和目标表中都存在的部门都将使用新名称、新经理或这两者进行更新。如果没有变化，则不进行任何更新。这是通过 WHEN MATCHED THEN 子句完成的。

  • 在 Departments 中不存在但存在于 Departments_delta 中的所有部门，将插入到 Departments 中。这是通过 WHEN NOT MATCHED THEN 子句完成的。

  • 在 Departments_delta 中不存在但存在于 Departments 中的所有部门将从 Departments 中删除。这是通过 WHEN NOT MATCHED BY SOURCE THEN 子句完成的。

MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
    INSERT (DeptID, DeptName, Manager)
        VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, 
       inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName, 
       inserted.Manager AS SourceManager, 
       deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName, 
       deleted.Manager AS TargetManager;