【SQLServer】Merge语法

概述

MERGE语句，也被称为“upsert”，根据与源表联接的结果，对目标表进行插入、更新或删除操作。例如，根据与另一个表的区别，在一个表中插入、更新或删除行，从而同步两个表。
MERGE 语句允许将数据源与目标表或视图联接，然后根据该联接的结果对目标执行多项操作。
MERGE 语句可以执行以下操作：

有条件地在目标表中插入或更新行。
如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。
同步两个表。
根据与源数据的差别在目标表中插入、更新或删除行。

当两个表具有匹配特性的复杂混合时，针对 MERGE 语句介绍的条件行为的效果最佳。例如，插入不存在的行，或更新匹配的行。
如果仅根据另一个表的行更新另一个表时，可以使用 INSERT、UPDATE 和 DELETE 语句来提高性能和可伸缩性。
例如：
INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

Merge语法

-- MERGE 语句用于执行插入、更新或删除操作，根据目标表和源表之间的匹配结果。
MERGE
    -- TOP (expression) [PERCENT]：可选，指定返回结果的行数或百分比。
    [ TOP ( expression ) [ PERCENT ] ]
    -- INTO：关键字，表示合并操作将影响目标表。
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ] -- 这一行指定了目标表<target_table>，并且给它起了别名table_alias ，方便在后续的语句中使用。
    -- USING：关键字，指定要与目标表联接的数据源。
    USING <table_source> [ [ AS ] table_alias ]  -- 定义源数据
    -- ON：关键字，定义MERGE操作的条件，即当目标表target中的ID列与源数据source中的id列匹配时。
    ON <merge_search_condition> - 定义了MERGE操作的条件，即当目标表target中的ID列与源数据source中的id列匹配时
    -- WHEN MATCHED：当目标表中的记录与源表中的记录匹配时执行的语句。
    [ WHEN MATCHED [ AND <clause_search_condition> ] 
        THEN <merge_matched> ] [ ...n ]
    -- WHEN NOT MATCHED：当目标表中的记录与源表中的记录不匹配时执行的语句。
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ] -- 当条件不匹配时，即ID不存在时
        THEN <merge_not_matched> ] 
    -- WHEN NOT MATCHED BY SOURCE：当源表中的记录与目标表中的记录不匹配时执行的语句。
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ] 
    -- <output_clause>：可选，用于指定MERGE操作的结果输出。
    [ <output_clause> ]
    -- OPTION (<query_hint> [ ,...n ] )：可选，用于指定查询优化器使用的查询提示。
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

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

MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。
USING 子句用于指定要与目标联接的数据源。
ON 子句用于指定决定目标与源的匹配位置的联接条件。
WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。
OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。

注意
Merge语句必须指定三个 MATCHED 子句中的至少一个子句，但可以按任何顺序指定。无法在同一个 MATCHED 子句中多次更新一个变量。
并且MERGE 语句需要一个分号 (😉 作为语句终止符。如果运行没有终止符的 MERGE 语句，将引发错误 10713。

性能优化

搜索条件使用原则

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

在 ON <merge_search_condition> 子句中仅指定决定源表与目标表中数据的匹配的搜索条件;
不要包括与其他值（如常量）的比较。

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

在适当的 WHEN 子句中指定用于行筛选的搜索条件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE ‘S%’ THEN INSERT…;
对返回筛选行的源表或目标表定义视图，并且将该视图作为源表或目标表进行引用。如果该视图是针对目标表定义的，则针对该视图的任何操作都必须满足更新视图所需的条件;
使用 WITH <通用表表达式> 子句从源表或目标表中筛选出行。此方法类似于在 ON 子句中指定附加搜索条件，并可能产生不正确的结果。建议您避免使用此方法，或者在采用它前进行全面测试。

MERGE 语句中联接操作的优化方式与 SELECT 语句中联接操作的优化方式相同。也就是说，当 SQL Server 处理联接时，查询优化器从多种可行方法中选择最高效的方法来处理联接。如果源表和目标表的大小相似，且前面介绍的索引准则已应用于源表和目标表，那么合并联接运算符是最高效的查询计划。这是由于对两个表都只扫描一次，并且无需对数据进行排序。如果源表小于目标表，最好使用嵌套循环运算符。
通过在 MERGE 语句中指定 OPTION (<query_hint>) 子句，可以强制使用某种特定联接。建议不要将哈希联接用作 MERGE 语句的查询提示，因为该联接类型不使用索引。

有关索引的最佳做法

通过使用 MERGE 语句，可以使用单个语句替换各个 DML 语句。由于操作是在单个语句中执行的，因此可以提高查询性能，从而最大限度地减少处理源表和目标表中数据的次数。然而，性能的提升取决于是否进行了正确的索引和联接以及是否遵守了其他注意事项。
若要提高 MERGE 语句的性能，我们建议您遵循以下索引准则：

创建索引以促进 MERGE 的源与目标之间的联接：
- 在源表的联接列上创建索引，该索引具有涵盖目标表的联接逻辑的键。如果可能，该索引应该是唯一的。
- 此外，在目标表中的联接列上创建索引。如果可能，该索引应该是唯一的聚集索引。
- 这两个索引可确保对表中的数据进行排序，而唯一性有助于进行比较。因为查询优化器不需要执行额外验证处理即可定位和更新重复的行，也不需要执行其他排序操作，所以查询性能得到了提高。
避免将具有任何形式的列存储索引的表作为 MERGE 语句的目标。与任何 UPDATE 一样，通过更新暂存行存储表，然后执行批量 DELETE 和 INSERT（而不是 UPDATE 或 MERGE）操作，你可能会发现列存储索引的性能更好。

使用示例

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

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

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');

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');

这两个表中有两个共有的产品-客户行：分别于本周和上周，客户 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.*;

接口查看：

select * from FactBuyingHabits;

+---------+----------+-----------------------+
|ProductID|CustomerID|LastPurchaseDate       |
+---------+----------+-----------------------+
|707      |11794     |2006-08-21 00:00:00.000|
|707      |15160     |2006-08-25 00:00:00.000|
|707      |18178     |2006-08-18 00:00:00.000|
|708      |18529     |2006-08-21 00:00:00.000|
|711      |11794     |2006-08-21 00:00:00.000|
|711      |19585     |2006-08-22 00:00:00.000|
|712      |14680     |2006-08-25 00:00:00.000|
|712      |19072     |2006-08-21 00:00:00.000|
|712      |21524     |2006-08-25 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      |11927     |2006-08-24 00:00:00.000|
|870      |15160     |2006-08-23 00:00:00.000|
|870      |17151     |2006-08-18 00:00:00.000|
|870      |18749     |2006-08-25 00:00:00.000|
|871      |13350     |2006-08-15 00:00:00.000|
|871      |21163     |2006-08-15 00:00:00.000|
|871      |21717     |2006-08-17 00:00:00.000|
|873      |23381     |2006-08-15 00:00:00.000|
+---------+----------+-----------------------+

执行 INSERT、UPDATE 和 DELETE 操作

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');


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

执行以下SQL：

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;

查看结果：

+------+------------------+--------+
|DeptID|DeptName          |Manager |
+------+------------------+--------+
|1     |Human Resources   |Margheim|
|2     |Sales             |Erickson|
|3     |Accounting        |Varkey  |
|4     |Purchasing        |Barber  |
|6     |Production        |Jones   |
|7     |Customer Relations|Smith   |
+------+------------------+--------+