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

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) 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 [ BY TARGET ] 子句。The MERGE statement can have only one WHEN NOT MATCHED [ BY TARGET ] 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.

优化 MERGE 语句性能Optimizing MERGE Statement Performance

使用 MERGE 语句,可以将各个 DML 语句替换为一个语句。By using the MERGE statement, you can replace the individual DML statements with a single statement. 这可以提升查询性能,因为运算是在一个语句中执行的,从而最大限度地减少源表和目标表中数据的处理次数。This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. 不过,性能的提升取决于是否有正确的索引、联接和其他考虑因素。However, performance gains depend on having correct indexes, joins, and other considerations in place.

索引最佳做法Index Best Practices

为了提升 MERGE 语句的性能,建议遵循以下索引准则:To improve the performance of the MERGE statement, we recommend the following index guidelines:

  • 对源表的联接列创建唯一且涵盖的索引。Create an index on the join columns in the source table that is unique and covering.
  • 对目标表的联接列创建唯一的聚集索引。Create a unique clustered index on the join columns in the target table.

这些索引可确保联接键是唯一的,且表中数据已经过排序。These indexes ensure that the join keys are unique and the data in the tables is sorted. 查询性能之所以得到提升是因为,查询优化器不需要执行额外的验证处理,即可找到和更新重复行,也不需要执行额外的排序操作。Query performance is improved because the query optimizer does not need to perform extra validation processing to locate and update duplicate rows and additional sort operations are not necessary.

JOIN 最佳做法JOIN Best Practices

为了提升 MERGE 语句的性能,并确保获得正确的结果,建议遵循以下联接准则:To improve the performance of the MERGE statement and ensure correct results are obtained, we recommend the following join guidelines:

  • 只在 ON <merge_search_condition> 子句中指定搜索条件,确定用于匹配源表与目标表中数据的条件。Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. 也就是说,只指定与源表的对应列相比较的目标表中的列。That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
  • 不要包括与其他值(如常量)的比较。Do not include comparisons to other values such as a constant.

若要筛选掉源表或目标表中的行,请使用以下方法之一。To filter out rows from the source or target tables, use one of the following methods.

  • 在适当的 WHEN 子句中,指定用于行筛选的搜索条件。Specify the search condition for row filtering in the appropriate WHEN clause. 例如,WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • 对源表或目标表定义返回筛选后的行的视图,并将视图引用为源表或目标表。Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. 如果视图是对目标表定义的,那么对它执行的任何操作都必须满足视图更新条件。If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. 若要详细了解如何使用视图来更新数据,请参阅“通过视图修改数据”。For more information about updating data by using a view, see Modifying Data Through a View.
  • 使用 WITH <common table expression> 子句筛选掉源表或目标表中的行。Use the WITH <common table expression> clause to filter out rows from the source or target tables. 这种方法类似于在 ON 子句中指定附加搜索条件,可能会生成不正确的结果。This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. 建议避免使用这种方法,或在实现这种方法前进行全面测试。We recommend that you avoid using this method or test thoroughly before implementing it.

MERGE 语句中联接运算的优化方式与 SELECT 语句中的联接相同。The join operation in the MERGE statement is optimized in the same way as a join in a SELECT statement. 也就是说,当 SQL Server 处理联接时,查询优化器从多种可行方法中选择最高效的方法来处理联接。That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. 如果源表和目标表的大小相似,且前面介绍的索引准则已应用于源表和目标表,那么合并联接运算符是最高效的查询计划。When the source and target are of similar size and the index guidelines described previously are applied to the source and target tables, a merge join operator is the most efficient query plan. 这是因为两个表都只扫描一次,且无需对数据进行排序。This is because both tables are scanned once and there is no need to sort the data. 如果源表小于目标表,最好使用嵌套循环运算符。When the source is smaller than the target table, a nested loops operator is preferable.

通过在 MERGE 语句中指定 OPTION (<query_hint>) 子句,可以强制使用特定联接。You can force the use of a specific join by specifying the OPTION (<query_hint>) clause in the MERGE statement. 建议不要将哈希联接用作 MERGE 语句的查询提示,因为这种类型的联接不使用索引。We recommend that you do not use the hash join as a query hint for MERGE statements because this join type does not use indexes.

参数化最佳做法Parameterization Best Practices

如果在没有参数的情况下执行 SELECT、INSERT、UPDATE 或 DELETE 语句,SQL Server 查询优化器可能会选择在内部参数化语句。If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally. 也就是说,查询中包含的任何文本值都将被参数替换。This means that any literal values that are contained in the query are substituted with parameters. 例如,可以在内部将语句 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) 实现为 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)。For example, the statement INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), may be implemented internally as INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). 此过程称为“简单参数化”,它增强了关系引擎将新 SQL 语句与先前编译的现有执行计划进行匹配的能力。This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. 查询性能可能会得到提升,因为查询编译和重新编译的频率降低了。Query performance may be improved because the frequency of query compilations and recompilations are reduced. 查询优化器不会对 MERGE 语句应用简单参数化过程。The query optimizer does not apply the simple parameterization process to MERGE statements. 因此,包含文本值的 MERGE 语句可能没有各个 INSERT、UPDATE 或 DELETE 语句执行得好,因为每次执行 MERGE 语句都会编译一个新计划。Therefore, MERGE statements that contain literal values may not perform as well as individual INSERT, UPDATE, or DELETE statements because a new plan is compiled each time the MERGE statement is executed.

为了提升查询性能,建议遵循以下参数化准则:To improve query performance, we recommend the following parameterization guidelines:

  • 参数化 MERGE 语句的 ON <merge_search_condition> 子句和 WHEN 子句中的所有文本值。Parameterize all literal values in the ON <merge_search_condition> clause and in the the WHEN clauses of the MERGE statement. 例如,可以将 MERGE 语句合并到存储过程中,用适当的输入参数替换文本值。For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.
  • 如果无法参数化语句,请创建 TEMPLATE 类型的计划指南,并在计划指南中指定 PARAMETERIZATION FORCED 查询提示。If you cannot parameterize the statement, create a plan guide of type TEMPLATE and specify the PARAMETERIZATION FORCED query hint in the plan guide.
  • 如果对数据库频繁执行 MERGE 语句,建议将数据库的 PARAMETERIZATION 选项设置为 FORCED。If MERGE statements are executed frequently on the database, consider setting the PARAMETERIZATION option on the database to FORCED. 请谨慎设置此选项。Use caution when setting this option. PARAMETERIZATION 选项是数据库级别设置,它影响如何对数据库处理所有查询。The PARAMETERIZATION option is a database-level setting and affects how all queries against the database are processed.

TOP 子句最佳做法TOP Clause Best Practices

在 MERGE 语句中,TOP 子句指定在对源表和目标表进行联接之后,以及在删除不符合执行插入、更新或删除操作条件的行之后,受影响的行数或所占的百分比。In the MERGE statement, the TOP clause specifies the number or percentage of rows that are affected after the source table and the target table are joined, and after rows that do not 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 and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. 也就是说,在 WHEN 子句中定义的操作之间,行是无序分布的。That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. 例如,指定 TOP (10) 会影响 10 行;在这些行中,可能会更新 7 行和插入 3 行,也可能会删除 1 行、更新 5 行和插入 4 行,依此类推。For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.

通常使用 TOP 子句对大型表分批执行数据操作语言 (DML) 运算。It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches. 如果为此在 MERGE 语句中使用 TOP 子句,请务必了解以下影响。When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • I/O 性能可能会受到影响。I/O performance may be affected.

    MERGE 语句对源表和目标表都执行全表扫描。The MERGE statement performs a full table scan of both the source and target tables. 将运算划分为多个批可以减少每批执行的写入操作数;但每个批处理都会对源表和目标表执行全表扫描。Dividing the operation into batches reduces the number of write operations performed per batch; however, each batch will perform a full table scan of the source and target tables. 由此产生的读取活动可能会影响查询性能。The resulting read activity may affect the performance of the query.

  • 可能出现不正确的结果。Incorrect results can occur.

    请务必确保所有连续批处理都以新行为目标,否则可能会发生意外行为,如在目标表中错误地插入重复行。It is important to ensure that all successive batches target new rows or undesired behavior such as incorrectly inserting duplicate rows into the target table can occur. 当源表包含不在目标批处理中但在整个目标表中的行时,可能会发生这种情况。This can happen when the source table includes a row that was not in a target batch but was in the overall target table.

  • 为了确保获得正确的结果,请执行以下操作:To insure correct results:

    • 使用 ON 子句确定哪些源行影响现有目标行,哪些是真正的新行。Use the ON clause to determine which source rows affect existing target rows and which are genuinely new.
    • 在 WHEN MATCHED 子句中使用附加条件,以确定目标行是否已由上一批处理进行更新。Use an additional condition in the WHEN MATCHED clause to determine if the target row has already been updated by a previous batch.

因为 TOP 子句只在这些子句应用之后才应用,所以每次执行要么插入一个真正不匹配的行,要么更新一个现有行。Because the TOP clause is only applied after these clauses are applied, each execution either inserts one genuinely unmatched row or updates one existing row.

大容量加载最佳做法Bulk Load Best Practices

MERGE 语句可以将 OPENROWSET(BULK…) 子句指定为表源,高效地将源数据文件中的数据大容量加载到目标表中。The MERGE statement can be used to efficiently bulk load data from a source data file into a target table by specifying the OPENROWSET(BULK…) clause as the table source. 通过这种方式,可以在一个批处理中处理整个文件。By doing so, the entire file is processed in a single batch.

为了提升大容量合并流程的性能,建议遵循以下准则:To improve the performance of the bulk merge process, we recommend the following guidelines:

  • 对目标表中的联接列创建聚集索引。Create a clustered index on the join columns in the target table.

  • OPENROWSET(BULK…) 子句中使用 ORDER 和 UNIQUE 提示,以指定如何对源数据文件进行排序。Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

    默认情况下,大容量操作假定数据文件未排序。By default, the bulk operation assumes the data file is unordered. 因此,请务必根据目标表的聚集索引对源数据进行排序,并使用 ORDER 提示来指明顺序,以便查询优化器能够生成更高效的查询计划。Therefore, it is important that the source data is sorted according to the clustered index on the target table and that the ORDER hint is used to indicate the order so that the query optimizer can generate a more efficient query plan. 提示是在运行时进行验证;如果数据流不符合指定提示,便会引发错误。Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.

上述准则可确保联接键是唯一的,且源文件中数据的排序顺序与目标表一致。These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. 查询性能之所以得到提升是因为,不需要执行额外的排序操作,也不需要执行不必要的数据复制。Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required.

度量和诊断 MERGE 性能Measuring and Diagnosing MERGE Performance

下面的功能有助于度量和诊断 MERGE 语句的性能。The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • sys.dm_exec_query_optimizer_info 动态管理视图中使用 merge stmt 计数器,以返回用于 MERGE 语句的查询优化数。Use the merge stmt counter in the sys.dm_exec_query_optimizer_info dynamic management view to return the number of query optimizations that are for MERGE statements.
  • sys.dm_exec_plan_attributes 动态管理视图中使用 merge_action_type 属性,以返回用作 MERGE 语句结果的触发器执行计划的类型。Use the merge_action_type attribute in the sys.dm_exec_plan_attributes dynamic management view to return the type of trigger execution plan used as the result of a MERGE statement.
  • 使用 SQL 跟踪来收集 MERGE 语句的疑难解答数据,就像对其他数据操作语言 (DML) 语句收集数据一样。Use SQL Trace to gather troubleshooting data for the MERGE statement in the same way you would for other data manipulation language (DML) statements. 有关详细信息,请参阅 SQL TraceFor more information, see SQL Trace.

示例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