MERGE (Transact-SQL)

语法

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

<target_table> ::=
{ 
    [ database_name . schema_name . | schema_name . ]
    target_table
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML <openxml_clause> 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | <joined_table> 
  | <pivoted_table> 
  | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
  { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                        | field_name = expression }
                        | method_name ( argument [ ,...n ] ) }
    }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
  ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
    { <column_name> | scalar_expression } 
        [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

参数

• WITH <common_table_expression>
  指定在 MERGE 语句作用域内定义的临时命名结果集或视图，也称为公用表表达式。该结果集派生自一个简单查询，并由 MERGE 语句引用。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

• TOP ( expression ) [ PERCENT ]
  指定受影响的行数或行百分比。expression 可以为行数，也可以为行百分比。在 TOP 表达式中引用的行不是以任意顺序排列的。有关详细信息，请参阅 TOP (Transact-SQL)。

  在联接整个源表和整个目标表并且删除了不符合插入、更新或删除操作条件的联接行之后，应用 TOP 子句。TOP 子句将联接行的数量进一步减少为指定值，并且以一种无序方式对其余联接行应用插入、更新或删除操作。也就是说，在 WHEN 子句中定义的操作中，这些行是无序分布的。例如，如果指定 TOP (10)，将会影响 10 行；在这些行中，可能会更新 7 行而插入 3 行，或者可能删除 1 行、更新 5 行并且插入 4 行，等等。

  由于 MERGE 语句对源表和目标表都进行完全表扫描，因此在使用 TOP 子句通过创建多个批处理来修改大型表时，I/O 性能可能会受到影响。在这种情况下，一定要确保所有连续批处理都以新行作为处理目标。有关详细信息，请参阅优化 MERGE 语句性能。

• database_name
  target_table 所在数据库的名称。

• schema_name
  target_table 所属架构的名称。

• target_table
  表或视图，<table_source> 中的数据行将根据 <clause_search_condition> 与该表或视图进行匹配。target_table 是由 MERGE 语句的 WHEN 子句指定的任何插入、更新或删除操作的目标。

  如果 target_table 为视图，则针对它的任何操作都必须满足更新视图所需的条件。有关详细信息，请参阅通过视图修改数据。

  target_table 不能是远程表。target_table 不能具有针对它定义的任何规则。

• [ AS ] table_alias
  用于引用表的替代名称。

• USING <table_source>
  指定基于 <merge_search condition> 与 target_table 中的数据行进行匹配的数据源。此匹配的结果指出了要由 MERGE 语句的 WHEN 子句采取的操作。<table_source> 可以是一个远程表，或者是一个能够访问远程表的派生表。

  <table_source> 可以是一个派生表，它使用 Transact-SQL表值构造函数通过指定多行来构造表。

  有关此子句的语法和参数的详细信息，请参阅 FROM (Transact-SQL)。

• ON <merge_search_condition>
  指定在 <table_source> 与 target_table 进行联接以确定它们的匹配位置时所遵循的条件。

  注意
  重要的是，应仅指定目标表中用于匹配目的的列。也就是说，指定与源表中的对应列进行比较的目标表列。不要尝试通过在 ON 子句中筛选掉目标表中的行来提高查询性能，例如，指定 AND NOT target_table.column_x = value。这样做可能会返回意外和不正确的结果。

• WHEN MATCHED THEN <merge_matched>
  指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行匹配并满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。

  MERGE 语句最多可以有两个 WHEN MATCHED 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <search_condition> 子句。对于任何给定的行，只有在未应用第一个 WHEN MATCHED 子句的情况下，才会应用第二个 WHEN MATCHED 子句。如果有两个 WHEN MATCHED 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。如果在 <merge_matched> 子句中指定了 UPDATE，并且根据 <merge_search_condition>，<table_source> 中的多个行与 target_table 中的某一行匹配，则 SQL Server 将返回错误。MERGE 语句无法多次更新同一行，也无法更新和删除同一行。

• WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
  指定对于 <table_source> ON <merge_search_condition> 返回的每一行，如果该行与 target_table 中的行不匹配但是满足其他搜索条件（如果存在），则在 target_table 中插入一行。要插入的值是由 <merge_not_matched> 子句指定的。MERGE 语句只能有一个 WHEN NOT MATCHED 子句。

• WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
  指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。

  MERGE 语句最多可以有两个 WHEN NOT MATCHED BY SOURCE 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <clause_search_condition> 子句。对于任何给定的行，只有当未应用第一个 WHEN NOT MATCHED BY SOURCE 子句时才会应用第二个子句。如果有两个 WHEN NOT MATCHED BY SOURCE 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。在 <clause_search_condition> 中只能引用目标表中的列。

  当 <table_source> 未返回任何行时，无法访问源表中的列。如果 <merge_matched> 子句中指定的更新或删除操作引用了源表中的列，将返回错误 207（无效列名）。例如，由于无法访问源表中的 Col1，因此 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 子句可能导致该语句失败。

• AND <clause_search_condition>
  指定任何有效的搜索条件。有关详细信息，请参阅搜索条件 (Transact-SQL)。

• <table_hint_limited>
  为由 MERGE 语句执行的每个插入、更新或删除操作指定对目标表应用的一个或多个表提示。需要有 WITH 关键字和括号。

  不允许使用 NOLOCK 和 READUNCOMMITTED。有关表提示的详细信息，请参阅表提示 (Transact-SQL)。

  对作为 INSERT 语句目标的表指定 TABLOCK 提示与指定 TABLOCKX 提示具有相同的效果。对表采用排他锁。如果指定了 FORCESEEK，会将其应用于与源表联接的目标表的隐式实例。

  注意
  指定带有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能会导致违反 UNIQUE 约束的 INSERT 操作。

• INDEX ( index_val [ ,...n ] )
  指定目标表上的一个或多个索引的名称或 ID，以执行与源表的隐式联接。有关详细信息，请参阅表提示 (Transact-SQL)。

• <output_clause>
  不按照任何特定顺序为 target_table 中更新、插入或删除的每一行返回一行。有关此子句的参数的详细信息，请参阅 OUTPUT 子句 (Transact-SQL)。

• OPTION ( <query_hint> [ ,...n ] )
  指定使用优化器提示来自定义数据库引擎处理语句的方式。有关详细信息，请参阅查询提示 (Transact-SQL)。

• <merge_matched>
  指定更新或删除操作，这些操作应用于 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行。

  • UPDATE SET <set_clause>
    指定目标表中要更新的列或变量名的列表，以及用于更新它们的值。

    有关该子句的参数的详细信息，请参阅 UPDATE (Transact-SQL)。不允许将变量设置为与列相同的值。

  • DELETE
    指定删除与 target_table 中的行匹配的行。

• <merge_not_matched>
  指定要插入到目标表中的值。

  • ( column_list )
    要在其中插入数据的目标表中的一列或多列的列表。必须使用单一部分名称格式来指定这些列，否则 MERGE 语句将失败。column_list 必须用圆括号括起来，并用逗号进行分隔。

  • VALUES ( values_list )
    一个逗号分隔列表，其中包含常量、变量或者返回要插入到目标表中的值的表达式。表达式不能包含 EXECUTE 语句。

  • DEFAULT VALUES
    强制插入的行包含为每个列定义的默认值。

  有关此子句的详细信息，请参阅 INSERT (Transact-SQL)。

• <search condition>
  指定用于指定 <merge_search_condition> 或 <clause_search_condition> 的搜索条件。有关此子句的参数的详细信息，请参阅搜索条件 (Transact-SQL)。

注释

必须指定三个 MATCHED 子句中的至少一个子句，但可以按任何顺序指定。不能在同一个 MATCHED 子句中多次更新一个变量。

由 MERGE 语句指定的目标表中的任何插入、更新或删除操作都受为它定义的任何约束的限制，包括任何级联引用完整性约束。如果 IGNORE_DUP_KEY 对于目标表中的任何唯一索引都设置为 ON，则 MERGE 将忽略此设置。

MERGE 语句需要一个分号 (;) 作为语句终止符。如果运行没有终止符的 MERGE 语句，将引发错误 10713。

如果在 MERGE 之后使用，@@ROWCOUNT (Transact-SQL) 会返回为客户端插入、更新和删除的行的总数。

在数据库兼容级别设置为 100 时，MERGE 为完全保留的关键字。MERGE 语句可用于设置为 90 和 100 的数据库兼容级别；但是，在将数据库兼容级别设置为 90 时，MERGE 为非完全保留的关键字。

触发器的实现

权限

需要对源表的 SELECT 权限和对目标表的 INSERT、UPDATE 或 DELETE 权限。有关其他信息，请参阅 SELECT、INSERT、UPDATE 和 DELETE 主题中的“权限”部分。

示例

A. 使用 MERGE 在单个语句中对表执行 INSERT 和 UPDATE 操作

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.   
    IF (@@ROWCOUNT = 0 )
    BEGIN
        INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN   
        INSERT (UnitMeasureCode, Name)
        VALUES (source.UnitMeasureCode, source.Name)
        OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
-- Cleanup 
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
GO

B. 使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作

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. 借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作

USE AdventureWorks2008R2;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

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 INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. 将 MERGE 语句的执行结果插入到另一个表中

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

请参阅

参考

概念