MERGE (Transact-SQL)MERGE (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

根据与源表联接的结果,对目标表运行插入、更新或删除操作。Runs insert, update, or delete operations on a target table from the results of a join with a source table. 例如,根据与另一个表的区别,在一个表中插入、更新或删除行,从而同步两个表。For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

性能提示: 当两个表具有匹配特性的复杂混合时,针对 MERGE 语句介绍的条件行为的效果最佳。Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. 例如,插入不存在的行,或更新匹配的行。For example, inserting a row if it doesn't exist, or updating a row if it matches. 仅根据另一个表的行更新一个表时,通过基本的 INSERT、UPDATE 和 DELETE 语句提升性能和可伸缩性。When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements. 例如:For example:

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

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

[ 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> ::=  
    MATCH(<graph_search_pattern>) | <search_condition_without_match> | <search_condition> AND <search_condition>
    
<search_condition_without_match> ::=
    { [ NOT ] <predicate> | ( <search_condition_without_match> ) 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition_without_match> ) } ]   
[ ,...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 ) }   

<graph_search_pattern> ::=
    { <node_alias> { 
                      { <-( <edge_alias> )- } 
                    | { -( <edge_alias> )-> }
                    <node_alias> 
                   } 
    }
  
<node_alias> ::=
    node_table_name | node_table_alias 

<edge_alias> ::=
    edge_table_name | edge_table_alias

<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  

参数Arguments

WITH <common_table_expression>WITH <common_table_expression>
指定在 MERGE 语句作用域内定义的临时命名结果集或视图,亦称为“公用表表达式”。Specifies the temporary named result set or view, also known as common table expression, that's defined within the scope of the MERGE statement. 结果集派生自简单查询,并由 MERGE 语句引用。The result set derives from a simple query and is referenced by the MERGE statement. 有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
指定受影响的行数或所占百分比。Specifies the number or percentage of affected rows. expression 可以是行数或行百分比。expression can be either a number or a percentage of the rows. 在 TOP 表达式中引用的行不是以任意顺序排列的。The rows referenced in the TOP expression are not arranged in any order. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

在整个源表和目标表联接,且不符合插入、更新或删除操作条件的联接行遭删除后,应用 TOP 子句。The TOP clause applies after the entire source table and the entire target table join and the joined rows that don't qualify for an insert, update, or delete action are removed. TOP 子句进一步将联接行数减少到指定值。The TOP clause further reduces the number of joined rows to the specified value. 插入、更新或删除操作以无序方式应用于其余联接行。The insert, update, or delete actions apply to the remaining joined rows in an unordered way. 也就是说,在 WHEN 子句中定义的操作中,这些行是无序分布的。That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. 例如,指定 TOP (10) 会影响 10 行。For example, specifying TOP (10) affects 10 rows. 在这些行中,可能会更新 7 行并插入 3 行,也可能会删除 1 行、更新 5 行并插入 4 行等。Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.

由于 MERGE 语句对源表和目标表都进行完全表扫描,因此在使用 TOP 子句通过创建多个批处理来修改大型表时,I/O 性能有时会受到影响。Because the MERGE statement does a full table scan of both the source and target tables, I/O performance is sometimes affected when using the TOP clause to modify a large table by creating multiple batches. 在这种情况下,请务必要确保所有连续批处理都以新行为目标。In this scenario, it's important to ensure that all successive batches target new rows.

database_namedatabase_name
target_table 所在数据库的名称。The name of the database in which target_table is located.

schema_nameschema_name
target_table 所属架构的名称。The name of the schema to which target_table belongs.

target_tabletarget_table
<table_source> 中的数据行根据 <clause_search_condition> 进行匹配的表或视图。The table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table 是由 MERGE 语句的 WHEN 子句指定的任何插入、更新或删除操作的目标。target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

如果 target_table 为视图,则针对它的任何操作都必须满足更新视图所需的条件。If target_table is a view, any actions against it must satisfy the conditions for updating views. 有关详细信息,请参阅通过视图修改数据For more information, see Modify Data Through a View.

target_table 不得是远程表。target_table can't be a remote table. target_table 不得有任何针对它定义的规则。target_table can't have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
用于引用表的替代名称。An alternative name to reference a table.

USING <table_source>USING <table_source>
指定根据 <merge_search condition> 与 target_table 中的数据行进行匹配的数据源。Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. 此匹配的结果指出了要由 MERGE 语句的 WHEN 子句采取的操作。The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> 可以是一个远程表,或者是一个能够访问远程表的派生表。<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> 可以是一个派生表,它使用 Transact-SQLTransact-SQL 表值构造函数通过指定多行来构造表。<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

有关此子句的语法和参数的详细信息,请参阅 FROM (Transact-SQL)For more information about the syntax and arguments of this clause, see FROM (Transact-SQL).

ON <merge_search_condition>ON <merge_search_condition>
指定联接 <table_source> 与 target_table 以确定匹配位置所要满足的条件。Specifies the conditions on which <table_source> joins with target_table to determine where they match.

注意

请务必仅指定目标表中用于匹配目的的列。It's important to specify only the columns from the target table to use for matching purposes. 也就是说,指定与源表中的对应列进行比较的目标表列。That is, specify columns from the target table that are compared to the corresponding column of the source table. 请勿尝试通过在 ON 子句中筛选掉目标表中的行(如指定 AND NOT target_table.column_x = value)来提高查询性能。Don't attempt to improve query performance by filtering out rows in the target table in the ON clause; for example, such as specifying AND NOT target_table.column_x = value. 这样做可能会返回意外和不正确的结果。Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>WHEN MATCHED THEN <merge_matched>
指定根据 <merge_matched> 子句更新或删除 *target_table 中所有与 <table_source> ON <merge_search_condition> 返回的行匹配、且满足其他所有搜索条件的行。Specifies that all rows of *target_table, which match the rows returned by <table_source> ON <merge_search_condition>, and satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

MERGE 语句最多可以有两个 WHEN MATCHED 子句。The MERGE statement can have, at most, two WHEN MATCHED clauses. 如果指定了两个子句,第一个子句必须随附 AND <search_condition> 子句。If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. 对于任何给定行,只有在未应用第一个 WHEN MATCHED 子句时,才会应用第二个 WHEN MATCHED 子句。For any given row, the second WHEN MATCHED clause is only applied if the first isn't. 如果有两个 WHEN MATCHED 子句,一个必须指定 UPDATE 操作,另一个必须指定 DELETE 操作。If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. 如果 <merge_matched> 子句中指定的是 UPDATE,且根据 <merge_search_condition> <table_source> 中有多行与 target_table 中的一行匹配,SQL ServerSQL Server 便会返回错误。When UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL ServerSQL Server returns an error. MERGE 语句无法多次更新同一行,也无法更新和删除同一行。The MERGE statement can't update the same row more than once, or update and delete the same row.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
指定针对 <table_source> ON <merge_search_condition> 返回且不与 target_table 中的行匹配、但满足其他搜索条件(若有)的所有行,将一行插入 target_table 中。Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that doesn't match a row in target_table, but satisfies an additional search condition, if present. 要插入的值是由 <merge_not_matched> 子句指定的。The values to insert are specified by the <merge_not_matched> clause. MERGE 语句只能有一个 WHEN NOT MATCHED 子句。The MERGE statement can have only one WHEN NOT MATCHED clause.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
指定根据 <merge_matched> 子句更新或删除 *target_table 中所有不与 <table_source> ON <merge_search_condition> 返回的行匹配、但满足其他所有搜索条件的行。Specifies that all rows of *target_table, which don't match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are updated or deleted according to the <merge_matched> clause.

MERGE 语句最多可以有两个 WHEN NOT MATCHED BY SOURCE 子句。The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. 如果指定了两个子句,则第一个子句必须同时带有一个 AND <clause_search_condition> 子句。If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. 对于任何给定行,只有在未应用第一个 WHEN NOT MATCHED BY SOURCE 子句时,才会应用第二个 WHEN NOT MATCHED BY SOURC 子句。For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first isn't. 如果有两个 WHEN NOT MATCHED BY SOURCE 子句,那么其中的一个必须指定 UPDATE 操作,而另一个必须指定 DELETE 操作。If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. 在 <clause_search_condition> 中只能引用目标表中的列。Only columns from the target table can be referenced in <clause_search_condition>.

如果 <table_source> 未返回任何行,无法访问源表中的列。When no rows are returned by <table_source>, columns in the source table can't be accessed. 如果 <merge_matched> 子句中指定的更新或删除操作引用了源表中的列,则会返回错误 207(列名无效)。If the update or delete action specified in the <merge_matched> clause references columns in the source table, error 207 (Invalid column name) is returned. 例如,由于无法访问源表中的 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1,因此 Col1 子句可能导致该语句失败。For example, the clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 may cause the statement to fail because Col1 in the source table is inaccessible.

AND <clause_search_condition>AND <clause_search_condition>
指定任何有效的搜索条件。Specifies any valid search condition. 有关详细信息,请参阅搜索条件 (Transact-SQL)For more information, see Search Condition (Transact-SQL).

<table_hint_limited><table_hint_limited>
指定针对 MERGE 语句完成的每个插入、更新或删除操作,对目标表应用的一个或多个表提示。Specifies one or more table hints to apply on the target table for each of the insert, update, or delete actions done by the MERGE statement. 需要有 WITH 关键字和括号。The WITH keyword and the parentheses are required.

禁止使用 NOLOCK 和 READUNCOMMITTED。NOLOCK and READUNCOMMITTED aren't allowed. 有关表提示的详细信息,请参阅表提示 (Transact-SQL)For more information about table hints, see Table Hints (Transact-SQL).

对作为 INSERT 语句目标的表指定 TABLOCK 提示,与指定 TABLOCKX 提示的效果相同。Specifying the TABLOCK hint on a table that's the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. 对表采用排他锁。An exclusive lock is taken on the table. 如果指定了 FORCESEEK,它会应用于与源表联接的目标表的隐式实例。When FORCESEEK is specified, it applies to the implicit instance of the target table joined with the source table.

注意

指定带有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能会导致违反 UNIQUE 约束的 INSERT 操作。Specifying READPAST with WHEN NOT MATCHED [ BY TARGET ] THEN INSERT may result in INSERT operations that violate UNIQUE constraints.

INDEX ( index_val [,...n ] )INDEX ( index_val [ ,...n ] )
指定目标表上一个或多个索引的名称或 ID,以执行与源表的隐式联接。Specifies the name or ID of one or more indexes on the target table for doing an implicit join with the source table. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

<output_clause><output_clause>
针对 target_table 中不按照任何特定顺序更新、插入或删除的所有行返回一行。Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action 可在 output 子句中指定。$action can be specified in the output clause. $action 是类型为 nvarchar(10) 的列,它返回每一行中 3 个值中的一个:“INSERT”、“UPDATE”或“DELETE”(具体视对相应行完成的操作而定)。$action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action done on that row. 有关该子句的参数的详细信息,请参阅 OUTPUT 子句 (Transact-SQL)For more information about the arguments of this clause, see OUTPUT Clause (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )OPTION ( <query_hint> [ ,...n ] )
指定使用优化器提示来自定义数据库引擎处理语句的方式。Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

<merge_matched><merge_matched>
指定更新或删除操作,应用于 target_table 中所有不与 <table_source> ON <merge_search_condition> 返回的行匹配、但满足其他所有搜索条件的行。Specifies the update or delete action that's applied to all rows of target_table that don't match the rows returned by <table_source> ON <merge_search_condition>, and which satisfy any additional search condition.

UPDATE SET <set_clause>UPDATE SET <set_clause>
指定目标表中要更新的列或变量名称的列表,以及用来更新它们的值。Specifies the list of column or variable names to update in the target table and the values with which to update them.

有关该子句的参数的详细信息,请参阅 UPDATE (Transact-SQL)For more information about the arguments of this clause, see UPDATE (Transact-SQL). 不支持将变量设置为与列相同的值。Setting a variable to the same value as a column isn't supported.

删除DELETE
指定删除与 target_table 中的行匹配的行。Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
指定要插入到目标表中的值。Specifies the values to insert into the target table.

(column_list)(column_list)
要在其中插入数据的目标表中一个或多个列的列表。A list of one or more columns of the target table in which to insert data. 必须使用单一部分名称格式来指定这些列,否则 MERGE 语句将失败。Columns must be specified as a single-part name or else the MERGE statement will fail. 必须用括号将 column_list 括起来,并且用逗号进行分隔。column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)VALUES ( values_list)
返回要插入到目标表中的值的常量、变量或表达式的逗号分隔列表。A comma-separated list of constants, variables, or expressions that return values to insert into the target table. 表达式不得包含 EXECUTE 语句。Expressions can't contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
强制插入的行包含为每个列定义的默认值。Forces the inserted row to contain the default values defined for each column.

有关此子句的详细信息,请参阅 INSERT (Transact-SQL)For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
指定用于指定 <merge_search_condition> 或 <clause_search_condition> 的搜索条件。Specifies the search conditions to specify <merge_search_condition> or <clause_search_condition>. 有关此子句的参数的详细信息,请参阅搜索条件 (Transact-SQL)For more information about the arguments for this clause, see Search Condition (Transact-SQL).

<graph search pattern><graph search pattern>
指定图匹配模式。Specifies the graph match pattern. 有关此子句的参数的详细信息,请参阅 MATCH (Transact-SQL)For more information about the arguments for this clause, see MATCH (Transact-SQL)

RemarksRemarks

必须指定三个 MATCHED 子句中的至少一个子句,但可以按任何顺序指定。At least one of the three MATCHED clauses must be specified, but they can be specified in any order. 无法在同一个 MATCHED 子句中多次更新一个变量。A variable can't be updated more than once in the same MATCHED clause.

MERGE 语句对目标表指定的任何插入、更新或删除操作受限于,在此语句中定义的任何约束,包括任何级联引用完整性约束。Any insert, update, or delete action specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. 如果 IGNORE_DUP_KEY 对目标表中的任何唯一索引都设置为 ON,MERGE 便会忽略此设置。If IGNORE_DUP_KEY is ON for any unique indexes on the target table, MERGE ignores this setting.

MERGE 语句需要一个分号 (;) 作为语句终止符。The MERGE statement requires a semicolon (;) as a statement terminator. 如果运行没有终止符的 MERGE 语句,将引发错误 10713。Error 10713 is raised when a MERGE statement is run without the terminator.

如果在 MERGE 之后使用,@@ROWCOUNT (Transact-SQL) 会返回为客户端插入、更新和删除的行的总数。When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

在数据库兼容级别设置为 100 或更高时,MERGE 为完全保留的关键字。MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. MERGE 语句可用于设置为 90 和 100 的数据库兼容性级别;不过,当数据库兼容性级别设置为 90 时,关键字不是完全保留。The MERGE statement is available under both 90 and 100 database compatibility levels; however, the keyword isn't fully reserved when the database compatibility level is set to 90.

使用已排入队列的更新复制时,请勿使用 MERGE 语句。Don't use the MERGE statement when using queued updating replication. MERGE 和已排入队列的更新触发器不兼容。The MERGE and queued updating trigger aren't compatible. 使用 insert 或 update 语句替换 MERGE 语句。Replace the MERGE statement with an insert or an update statement.

触发器的实现Trigger Implementation

对于在 MERGE 语句中指定的每个插入、更新或删除操作,SQL ServerSQL Server 都会触发对目标表定义的任何对应 AFTER 触发器,但不保证哪个操作最先或最后触发触发器。For every insert, update, or delete action specified in the MERGE statement, SQL ServerSQL Server fires any corresponding AFTER triggers defined on the target table, but doesn't guarantee on which action to fire triggers first or last. 为相同操作定义的触发器会遵循您指定的顺序进行触发。Triggers defined for the same action honor the order you specify. 有关设置触发器激发顺序的详细信息,请参阅指定第一个和最后一个触发器For more information about setting trigger firing order, see Specify First and Last Triggers.

如果目标表已针对 MERGE 语句完成的插入、更新或删除操作启用了对自己定义的 INSTEAD OF 触发器,它必须已针对 MERGE 语句中指定的所有操作启用了 INSTEAD OF 触发器。If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action done by a MERGE statement, it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

如果对 target_table 定义了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 触发器,则不会运行更新或删除操作。If any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers are defined on target_table, the update or delete operations aren't run. 而是会触发触发器,并相应地填充 inserted 和 deleted 表。Instead, the triggers fire and the inserted and deleted tables then populate accordingly.

如果对 target_table 定义了任何 INSTEAD OF INSERT 触发器,则不会执行插入操作。If any INSTEAD OF INSERT triggers are defined on target_table, the insert operation isn't performed. 而是相应地填充表。Instead, the table populates accordingly.

权限Permissions

需要对源表的 SELECT 权限和对目标表的 INSERT、UPDATE 或 DELETE 权限。Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. 有关详细信息,请参阅 SELECTINSERTUPDATEDELETE 文章中的“权限”部分。For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

示例Examples

A.A. 使用 MERGE 在一个语句中对表执行 INSERT 和 UPDATE 操作Using MERGE to do INSERT and UPDATE operations on a table in a single statement

常见方案是,更新表中的一个或多个列(若有匹配行)。A common scenario is updating one or more columns in a table if a matching row exists. 或者,在没有匹配行的情况下,将数据作为新行插入。Or, inserting the data as a new row if a matching row doesn't exist. 处理两种方案之一的一般方法是,将参数传递给包含相应 UPDATE 和 INSERT 语句的存储过程。You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. 借助 MERGE 语句,可以在一个语句中同时执行这两项任务。With the MERGE statement, you can do both tasks in a single statement. 下面的示例显示了 AdventureWorks2012AdventureWorks2012 数据库中一个同时包含 INSERT 语句和 UPDATE 语句的存储过程。The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. 随后,过程被修改为,使用一个 MERGE 语句运行等效的操作。The procedure is then modified to run the equivalent operations by using a single MERGE statement.

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.B. 使用 MERGE 在一个语句中对表执行 UPDATE 和 DELETE 操作Using MERGE to do UPDATE and DELETE operations on a table in a single statement

下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单,每天更新一次 AdventureWorks2012AdventureWorks2012 示例数据库中的 ProductInventory 表。The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012AdventureWorks2012 sample database, daily, based on orders that are processed in the SalesOrderDetail table. 通过减去每天对 Quantity 表中的每种产品所下的订单数,更新 ProductInventory 表的 SalesOrderDetail 列。The Quantity column of the ProductInventory table is updated by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. 如果某种产品的订单数导致该产品的库存量下降到 0 或更少,则从 ProductInventory 表中删除该产品对应的行。If the number of orders for a product drops the inventory level of a product to 0 or less, the row for that product is deleted from the ProductInventory table.

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.C. 借助派生的源表,使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作Using MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

下面的示例使用 MERGE 以更新或插入行的方式来修改 AdventureWorks2012AdventureWorks2012 数据库中的 SalesReason 表。The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. 当源表中的 NewName 值与目标表 (Name) 的 SalesReason 列中的值匹配时,就会更新此目标表中的 ReasonType 列。When the value of NewName in the source table matches a value in the Name column of the target table, (SalesReason), the ReasonType column is updated in the target table. 如果 NewName 的值不匹配,源行就会被插入目标表中。When the value of NewName doesn't match, the source row is inserted into the target table. 此源表是一个派生表,它使用 Transact-SQLTransact-SQL 表值构造函数指定源表的多个行。The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. 有关在派生表中使用表值构造函数的详细信息,请参阅表值构造函数 (Transact-SQL)For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). 下面的示例还展示了如何在表变量中存储 OUTPUT 子句的结果。The example also shows how to store the results of the OUTPUT clause in a table variable. 然后,通过运行返回已插入行数和已更新行数的简单选择操作,汇总 MERGE 语句的结果。And, then you summarize the results of the MERGE statement by running a simple select operation that returns the count of inserted and updated rows.

-- 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.D. 将 MERGE 语句的执行结果插入到另一个表中Inserting the results of the MERGE statement into another table

下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据,并将该数据插入到另一个表中。The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. MERGE 语句根据在 Quantity 表中处理的订单更新 ProductInventory 数据库中 AdventureWorks2012AdventureWorks2012 表的 SalesOrderDetail 列。The MERGE statement updates the Quantity column of the ProductInventory table in the AdventureWorks2012AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail table. 下面的示例捕获已更新行,并将它们插入用于跟踪库存变化的另一个表中。The example captures the updated rows and inserts them into another table that's used to track inventory changes.

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  

E.E. 使用 MERGE 对图形数据库中的目标边缘表执行 INSERT 或 UPDATE 操作Using MERGE to do INSERT or UPDATE on a target edge table in a graph database

在此示例中,创建节点表 PersonCity 以及边缘表 livesInIn this example, you create node tables Person and City and an edge table livesIn. 如果 PersonCity 之间尚不存在 livesIn 边缘,则对边缘使用 MERGE 语句,并插入新行。You use the MERGE statement on the livesIn edge and insert a new row if the edge doesn't already exist between a Person and City. 如果已有边缘,只需更新 livesIn 边缘上的 StreetAddress 属性。If the edge already exists, then you just update the StreetAddress attribute on the livesIn edge.

-- CREATE node and edge tables
CREATE TABLE Person
    (
        ID INTEGER PRIMARY KEY, 
        PersonName VARCHAR(100)
    )
AS NODE
GO

CREATE TABLE City
    (
        ID INTEGER PRIMARY KEY, 
        CityName VARCHAR(100), 
        StateName VARCHAR(100)
    )
AS NODE
GO

CREATE TABLE livesIn
    (
        StreetAddress VARCHAR(100)
    )
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn 
WHERE MATCH(Person-(livesIn)->city)
GO

另请参阅See Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
OUTPUT 子句 (Transact-SQL) OUTPUT Clause (Transact-SQL)
在 Integration Services 包中执行 MERGE MERGE in Integration Services Packages
FROM (Transact-SQL) FROM (Transact-SQL)
表值构造函数 (Transact-SQL)Table Value Constructor (Transact-SQL)