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

Область применения: ДаSQL Server ДаБаза данных SQL Azure НетAzure Synapse Analytics (Хранилище данных SQL) НетParallel Data Warehouse 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-SQLTopic 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 для разделения на пакеты для изменения большой таблицы производительность ввода-вывода может снизиться.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 является целевым объектом любых операций вставки, обновления или удаления, указанных предложениями WHEN в инструкции MERGE.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>
Указывает источник данных, который сопоставляется со строками данных в таблице target_table на основе условия <merge_search condition>.Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. Результат этого совпадения обуславливает действия, которые выполняются предложениями WHEN инструкции MERGE.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>
Указывает, что все строки *target_table, которые соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.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 применяется только в том случае, если не применяется первое.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. Если действие UPDATE указано в предложении <merge_matched> и более одной строки из <table_source> соответствует строке в target_table на основе <merge_search_condition>, то 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>
Указывает, что в таблицу target_table вставляется строка для каждой строки, возвращенной выражением <table_source> ON <merge_search_condition>, которая не соответствует строке в таблице 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>
Указывает, что все строки таблицы *target_table, которые не соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition> и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.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 применяется только в том случае, если не применяется первое.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).

Указание TABLOCK для таблицы, к которой применяется инструкция INSERT, действует так же, как и указание 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.

Внимание!

Указание READPAST с предложением WHEN NOT MATCHED [ BY TARGET ] THEN INSERT может привести к выполнению операций INSERT, которые нарушают ограничения UNIQUE.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 ] )
Указывает имя или идентификатор одного или нескольких индексов целевой таблицы для выполнения неявного соединения с исходной таблицей.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 может быть указан в предложении вывода.$action can be specified in the output clause. $action — это столбец типа nvarchar(10) , который возвращает одно из трех значений для каждой строки: 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 ] )
Указывает, что для настройки способа, которым компонент Database Engine обрабатывает инструкцию, используются подсказки оптимизатора.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.

DELETEDELETE
Указывает, что строки, совпадающие со строками в 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. Возникает ошибка 10713, если инструкция MERGE выполняется без признака конца конструкции.Error 10713 is raised when a MERGE statement is run without the terminator.

Если функция @@ROWCOUNT (Transact-SQL) используется после инструкции MERGE, она возвращает общее количество вставленных, обновленных и удаленных строк из клиента.When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

Ключевое слово MERGE полностью резервируется, если установлен уровень совместимости базы данных 100 или выше.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. Замените инструкцию 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.

Если для целевой таблицы определен триггер INSTEAD OF для операций вставки, обновления или удаления, выполняемых инструкцией MERGE, то триггеры INSTEAD OF должны быть определены для всех операций, указанных в инструкции MERGE.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. Дополнительные сведения см. в разделе "Разрешения" статей об инструкциях SELECT, INSERT, UPDATE и DELETE.For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

Оптимизация производительности инструкции MERGEOptimizing 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 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.

Использование определенного соединения можно задать принудительно с помощью предложения OPTION (<query_hint>) в инструкции MERGE.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:

  • Выполните параметризацию всех литеральных значений в предложении ON <merge_search_condition> и в предложениях WHEN инструкции MERGE.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.

Рекомендации по использованию предложения TOPTOP 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. При использовании для этой цели предложения TOP в инструкции MERGE важно понимать следующие последствия.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • Может быть затронута производительность операций ввода-вывода.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.

Измерение и диагностика производительности инструкции MERGEMeasuring and Diagnosing MERGE Performance

Следующие доступные функции помогают производить измерение и диагностику производительности инструкций MERGE.The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • Используйте счетчик merge stmt в динамическом административном представлении sys.dm_exec_query_optimizer_info для возвращения числа оптимизаций запросов, произведенных для инструкций 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.
  • Используйте атрибут merge_action_type в динамическом административном представлении sys.dm_exec_plan_attributes для возвращения типа триггера плана выполнения, используемого в виде результата инструкции 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 Trace.For more information, see SQL Trace.

ПримерыExamples

A.A. Выполнение для таблицы операций INSERT и UPDATE с помощью одной инструкции MERGEUsing 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. Выполнение для таблицы операций UPDATE и DELETE с помощью одной инструкции MERGEUsing MERGE to do UPDATE and DELETE operations on a table in a single statement

В следующем примере инструкция MERGE ежедневно обновляет таблицу ProductInventory в примере базы данных AdventureWorks2012AdventureWorks2012, используя данные о заказах, которые обрабатываются в таблице SalesOrderDetail.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. Если количество заказов на продукт таково, что уровень запасов продукта опускается до нуля или становится еще ниже, то строка этого продукта удаляется из таблицы 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. Использование инструкции MERGE для выполнения операций UPDATE и INSERT в целевой таблице с помощью производной исходной таблицыUsing MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

В следующем примере инструкция MERGE используется для изменения таблицы SalesReason в базе данных AdventureWorks2012AdventureWorks2012 путем обновления или вставки строк.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. Вставка результатов инструкции MERGE в другую таблицуInserting the results of the MERGE statement into another table

В следующем примере производится отслеживание данных, возвращаемых предложением OUTPUT инструкции MERGE, а затем осуществляется вставка этих данных в другую таблицу.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. Выполнение инструкций INSERT или UPDATE в целевой таблице ребер в графовой базе данных с помощью MERGEUsing MERGE to do INSERT or UPDATE on a target edge table in a graph database

В этом примере мы создадим таблицы узлов Person и City, а также таблицу ребер livesIn.In this example, you create node tables Person and City and an edge table livesIn. Если ребро между таблицами Person и City не существует, мы добавляем новую строку в таблицу ребер 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. Если ребро уже существует, мы только обновим атрибут StreetAddress в таблице ребер livesIn.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