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

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Executa operações de inserção, atualização ou exclusão em uma tabela de destino usando os resultados de uma união com uma tabela de origem.Runs insert, update, or delete operations on a target table from the results of a join with a source table. Por exemplo, sincronize duas tabelas inserindo, atualizando ou excluindo linhas em uma tabela com base nas diferenças encontradas na outra tabela.For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Dica de desempenho: O comportamento condicional descrito para a instrução de MERGE funciona melhor quando as duas tabelas têm uma mistura complexa de características coincidentes.Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. Por exemplo, inserindo uma linha se ela não existir ou atualizando uma linha se ela tiver correspondência.For example, inserting a row if it doesn't exist, or updating a row if it matches. Ao simplesmente atualizar uma tabela com base nas linhas de outra tabela, melhore o desempenho e a escalabilidade com as instruções básicas INSERT, UPDATE e 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. Por exemplo: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);  

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

[ 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  

ArgumentosArguments

WITH <common_table_expression>WITH <common_table_expression>
Especifica a exibição ou o conjunto de resultados nomeado temporário, também conhecido como expressão de tabela comum, que é definida no escopo da instrução 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. O conjunto de resultados deriva de uma consulta simples e é referenciado pela instrução MERGE.The result set derives from a simple query and is referenced by the MERGE statement. Para obter mais informações, confira WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
Especifica o número ou a porcentagem de linhas afetadas.Specifies the number or percentage of affected rows. expression pode ser um número ou um percentual das linhas.expression can be either a number or a percentage of the rows. As linhas referenciadas na expressão TOP não são organizadas em nenhuma ordem.The rows referenced in the TOP expression are not arranged in any order. Para obter mais informações, confira TOP (Transact-SQL).For more information, see TOP (Transact-SQL).

A cláusula TOP aplica-se depois que toda a tabela de origem e toda a tabela de destino são unidas e as linhas unidas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas.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. A cláusula TOP ainda reduz o número de linhas unidas para o valor especificado.The TOP clause further reduces the number of joined rows to the specified value. As ações de inserção, atualização ou exclusão aplicam-se às linhas unidas restantes de maneira não ordenada.The insert, update, or delete actions apply to the remaining joined rows in an unordered way. Ou seja, não há ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN.That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. Por exemplo, a especificação de TOP (10) afeta 10 linhas.For example, specifying TOP (10) affects 10 rows. Dessas linhas, 7 podem ser atualizadas e 3 inseridas, ou 1 pode ser excluída, 5 atualizadas e 4 inseridas, e assim por diante.Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.

Como a instrução MERGE executa uma verificação de tabela completa das tabelas de origem e de destino, o desempenho de E/S às vezes é afetado quando se usa a cláusula TOP para modificar uma tabela grande criando vários lotes.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. Nesse cenário, é importante garantir que todos os lotes sucessivos se destinem a novas linhas.In this scenario, it's important to ensure that all successive batches target new rows.

database_namedatabase_name
O nome do banco de dados no qual target_table está localizado.The name of the database in which target_table is located.

schema_nameschema_name
O nome do esquema ao qual target_table pertence.The name of the schema to which target_table belongs.

target_tabletarget_table
A tabela ou exibição em relação à qual as linhas de dados de <table_source> são correspondidas com base na <clause_search_condition>.The table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table é o destino de qualquer operação de inserção, atualização ou exclusão especificada pelas cláusulas WHEN da instrução MERGE.target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

Se target_table for uma exibição, qualquer ação com ela deverá atender às condições para atualizar exibições.If target_table is a view, any actions against it must satisfy the conditions for updating views. Para obter mais informações, confira Modificar dados por meio de uma exibição.For more information, see Modify Data Through a View.

target_table não pode ser uma tabela remota.target_table can't be a remote table. target_table não pode ter regras definidas nela.target_table can't have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
Um nome alternativo para fazer referência a uma tabela.An alternative name to reference a table.

USING <table_source>USING <table_source>
Especifica a fonte de dados que é compatível com as linhas de dados em target_table com base em <merge_search condition>.Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. O resultado dessa correspondência dita as ações a serem tomadas pelas cláusulas WHEN da instrução MERGE.The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> pode ser uma tabela remota ou uma tabela derivada que acessa tabelas remotas.<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> pode ser uma tabela derivada que usa o Transact-SQLTransact-SQL construtor de valor de tabela para construir uma tabela especificando várias linhas.<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

Para obter mais informações sobre a sintaxe e os argumentos dessa cláusula, veja 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>
Especifica as condições nas quais <table_source> se une a target_table para determinar em que pontos há correspondências.Specifies the conditions on which <table_source> joins with target_table to determine where they match.

Cuidado

É importante especificar apenas as colunas da tabela de destino a serem usadas para fins de correspondência.It's important to specify only the columns from the target table to use for matching purposes. Ou seja, especifique as colunas da tabela de destino que são comparadas à coluna correspondente da tabela de origem.That is, specify columns from the target table that are compared to the corresponding column of the source table. Não tente melhorar o desempenho de consulta descartando linhas da tabela de destino na cláusula ON; por exemplo, especificando 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. Isso pode retornar resultados inesperados e incorretos.Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>WHEN MATCHED THEN <merge_matched>
Especifica que todas as linhas de *target_table, que correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer condição de pesquisa adicional, são atualizadas ou excluídas de acordo com a cláusula <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.

A instrução MERGE pode ter, no máximo, duas cláusulas WHEN MATCHED.The MERGE statement can have, at most, two WHEN MATCHED clauses. Se duas cláusulas forem especificadas, a primeira deverá ser acompanhada de uma cláusula AND <search_condition>.If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. Para qualquer linha especificada, a segunda cláusula WHEN MATCHED será aplicada somente se a primeira não for.For any given row, the second WHEN MATCHED clause is only applied if the first isn't. Se houver duas cláusulas WHEN MATCHED, uma delas deverá especificar uma ação UPDATE e a outra, uma ação DELETE.If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. Quando UPDATE for especificada na cláusula <merge_matched> e mais de uma linha de <table_source> corresponder a uma linha em target_table com base em <merge_search_condition>, SQL ServerSQL Server retornará um erro.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. A instrução MERGE não pode atualizar a mesma linha mais de uma vez, nem atualizar e excluir a mesma linha.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>
Especifica que uma linha é inserida em target_table para cada linha retornada por <table_source> ON <merge_search_condition> que não corresponde a uma linha em target_table, mas atende a um condição de pesquisa adicional, se houver.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. Os valores a serem inseridos são especificados pela cláusula <merge_not_matched>.The values to insert are specified by the <merge_not_matched> clause. A instrução MERGE só pode ter uma cláusula 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>
Especifica que todas as linhas de *target_table que não correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer condição de pesquisa adicional são atualizadas ou excluídas de acordo com a cláusula <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.

A instrução MERGE pode ter, no máximo, duas cláusulas WHEN NOT MATCHED BY SOURCE.The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. Se forem especificadas duas cláusulas, a primeira deverá ser acompanhada por uma cláusula AND <clause_search_condition>.If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. Para qualquer linha especificada, a segunda cláusula WHEN NOT MATCHED BY SOURCE será aplicada somente se a primeira não for.For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first isn't. Se houver duas cláusulas WHEN NOT MATCHED BY SOURCE, uma delas deverá especificar uma ação UPDATE e a outra, uma ação 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. Somente as colunas da tabela de destino podem ser referenciadas na <clause_search_condition>.Only columns from the target table can be referenced in <clause_search_condition>.

Quando nenhuma linha é retornada por <table_source>, não é possível acessar as colunas na tabela de origem.When no rows are returned by <table_source>, columns in the source table can't be accessed. Se a ação de atualização ou exclusão especificada na cláusula <merge_matched> referenciar colunas na tabela de origem, o erro 207 (Nome de coluna inválido) será retornado.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. Por exemplo, a cláusula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 pode fazer a instrução falhar porque não é possível acessar Col1 na tabela de origem.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>
Especifica qualquer critério de pesquisa válido.Specifies any valid search condition. Para obter mais informações, consulte Condição de pesquisa (Transact-SQL).For more information, see Search Condition (Transact-SQL).

<table_hint_limited><table_hint_limited>
Especifica uma ou mais dicas de tabela a serem aplicadas à tabela de destino para cada uma das ações de inserção, atualização ou exclusão executadas pela instrução 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. A palavra-chave WITH e parênteses são necessários.The WITH keyword and the parentheses are required.

NOLOCK e READUNCOMMITTED não são permitidas.NOLOCK and READUNCOMMITTED aren't allowed. Para obter mais informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).For more information about table hints, see Table Hints (Transact-SQL).

Especificar a dica TABLOCK em uma tabela que é o destino de uma instrução INSERT tem o mesmo efeito de especificar a dica 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. Um bloqueio exclusivo é obtido na tabela.An exclusive lock is taken on the table. Quando FORCESEEK é especificada, ela se aplica a uma instância implícita da tabela de destino unida à tabela de origem.When FORCESEEK is specified, it applies to the implicit instance of the target table joined with the source table.

Cuidado

Especificar READPAST com WHEN NOT MATCHED [ BY TARGET ] THEN INSERT pode resultar em operações INSERT que violam restrições 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 ] )
Especifica o nome ou a ID de um ou mais índices na tabela de destino para a execução de uma união implícita à tabela de origem.Specifies the name or ID of one or more indexes on the target table for doing an implicit join with the source table. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).For more information, see Table Hints (Transact-SQL).

<output_clause><output_clause>
Retorna uma linha para cada linha de target_table que é atualizada, inserida ou excluída, sem nenhuma ordem específica.Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action pode ser especificada na cláusula output.$action can be specified in the output clause. $action é uma coluna do tipo nvarchar(10) que retorna um dos três valores para cada linha: 'INSERT', 'UPDATE' ou 'DELETE', de acordo com a ação executada na linha.$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. Para obter mais informações sobre os argumentos dessa cláusula, veja Cláusula 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 ] )
Especifica que dicas do otimizador são usadas para personalizar o modo como o Mecanismo de Banco de Dados processa a instrução.Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. Para obter mais informações, veja Dicas de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

<merge_matched><merge_matched>
Especifica a ação de atualização ou exclusão que é aplicada a todas as linhas de target_table que não correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer condição de pesquisa adicional.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>
Especifica a lista de nomes de colunas ou de variáveis a serem atualizados na tabela de destino e os valores com os quais eles devem ser atualizados.Specifies the list of column or variable names to update in the target table and the values with which to update them.

Para obter mais informações sobre os argumentos dessa cláusula, veja UPDATE (Transact-SQL).For more information about the arguments of this clause, see UPDATE (Transact-SQL). Não é permitido definir uma variável com o mesmo valor de uma coluna.Setting a variable to the same value as a column isn't supported.

Delete (excluir)DELETE
Especifica que as linhas que correspondem a linhas em target_table são excluídas.Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
Especifica os valores a serem inseridos na tabela de destino.Specifies the values to insert into the target table.

(column_list)(column_list)
Uma lista de uma ou mais colunas da tabela de destino na qual inserir dados.A list of one or more columns of the target table in which to insert data. As colunas devem ser especificadas como um nome de parte única. Caso contrário, haverá falha na instrução MERGE.Columns must be specified as a single-part name or else the MERGE statement will fail. column_list deve ser colocada entre parênteses e separada por vírgulas.column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)VALUES ( values_list)
Uma lista de constantes, variáveis ou expressões separadas por vírgulas que retorna valores a serem inseridos na tabela de destino.A comma-separated list of constants, variables, or expressions that return values to insert into the target table. As expressões não podem conter uma instrução EXECUTE.Expressions can't contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
Força a linha inserida a conter os valores padrão definidos para cada coluna.Forces the inserted row to contain the default values defined for each column.

Para obter mais informações sobre essa cláusula, veja INSERT (Transact-SQL).For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
Especifica os critérios de pesquisa para especificar <merge_search_condition> ou <clause_search_condition>.Specifies the search conditions to specify <merge_search_condition> or <clause_search_condition>. Para obter mais informações sobre os argumentos para essa cláusula, veja Condição de pesquisa (Transact-SQL).For more information about the arguments for this clause, see Search Condition (Transact-SQL).

<graph search pattern><graph search pattern>
Especifica o padrão de correspondência do grafo.Specifies the graph match pattern. Para obter mais informações sobre os argumentos dessa cláusula, consulte MATCH (Transact-SQL)For more information about the arguments for this clause, see MATCH (Transact-SQL)

RemarksRemarks

Pelo menos uma das três cláusulas MATCHED devem ser especificadas, mas elas podem ser especificadas em qualquer ordem.At least one of the three MATCHED clauses must be specified, but they can be specified in any order. Uma variável não pode ser atualizada mais de uma vez na mesma cláusula MATCHED.A variable can't be updated more than once in the same MATCHED clause.

Qualquer ação de inserção, atualização ou exclusão especificada na tabela de destino pela instrução MERGE é limitada pelas restrições definidas nela, incluindo qualquer restrição de integridade referencial em cascata.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. Se IGNORE_DUP_KEY for ON em qualquer índice exclusivo na tabela de destino, MERGE vai ignorar essa configuração.If IGNORE_DUP_KEY is ON for any unique indexes on the target table, MERGE ignores this setting.

A instrução MERGE exige um ponto-e-vírgula (;) como terminador de instrução.The MERGE statement requires a semicolon (;) as a statement terminator. O erro 10713 ocorre quando uma instrução MERGE é executada sem o terminador.Error 10713 is raised when a MERGE statement is run without the terminator.

Quando usada depois de MERGE, @@ROWCOUNT (Transact-SQL) retorna o número total de linhas inseridas, atualizadas e excluídas para o cliente.When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

MERGE é uma palavra-chave totalmente reservada quando o nível de compatibilidade do banco de dados é definido como 100 ou superior.MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. A instrução MERGE está disponível abaixo dos níveis de compatibilidade do banco de dados 90 e 100. No entanto, a palavra-chave não é totalmente reservada quando o nível de compatibilidade do banco de dados está definido como 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.

Não use a instrução MERGE com a replicação de atualização enfileirada.Don't use the MERGE statement when using queued updating replication. A instrução MERGE e o gatilho de atualização enfileirada não são compatíveis.The MERGE and queued updating trigger aren't compatible. Substitua a instrução MERGE por uma instrução de inserção ou de atualização.Replace the MERGE statement with an insert or an update statement.

Implementação de gatilhoTrigger Implementation

Para cada ação de inserção, atualização ou exclusão especificada na instrução MERGE, SQL ServerSQL Server dispara qualquer gatilho AFTER correspondente definido na tabela de destino, mas não garante em qual ação os gatilhos serão disparados primeiro ou por último.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. Os gatilhos definidos para a mesma ação respeitam a ordem que você especifica.Triggers defined for the same action honor the order you specify. Para obter mais informações sobre a configuração da ordem de acionamento do gatilho, veja Especificar o primeiro e o último gatilho.For more information about setting trigger firing order, see Specify First and Last Triggers.

Se a tabela de destino tiver um gatilho INSTEAD OF habilitado definido para uma ação de inserção, atualização ou exclusão realizada por uma instrução MERGE, ela deverá ter um gatilho INSTEAD OF habilitado para todas as ações especificadas na instrução 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.

Se algum gatilho INSTEAD OF UPDATE ou INSTEAD OF DELETE for definido em target_table, as operações de atualização ou de exclusão não serão executadas.If any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers are defined on target_table, the update or delete operations aren't run. Em vez disso, os gatilhos serão disparados e as tabelas inserted e deleted serão populadas adequadamente.Instead, the triggers fire and the inserted and deleted tables then populate accordingly.

Se algum gatilho INSTEAD OF INSERT for definido em target_table, a operação de inserção não será executada.If any INSTEAD OF INSERT triggers are defined on target_table, the insert operation isn't performed. Em vez disso, a tabela será populada adequadamente.Instead, the table populates accordingly.

PermissõesPermissions

Requer a permissão SELECT na tabela de origem e as permissões INSERT, UPDATE ou DELETE na tabela de destino.Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. Para saber mais, confira a seção Permissões nos artigos SELECT, INSERT, UPDATE e DELETE.For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

Otimizar o desempenho da instrução MERGEOptimizing MERGE Statement Performance

Ao usar a instrução MERGE, você pode substituir as instruções DML individuais por uma única instrução.By using the MERGE statement, you can replace the individual DML statements with a single statement. Isso melhora o desempenho das consultas porque as operações são executadas em uma única instrução, diminuindo o número de vezes que os dados das tabelas de origem e de destino são processados.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. No entanto, os ganhos de desempenho dependem do uso de índices e junções corretos e de outras considerações.However, performance gains depend on having correct indexes, joins, and other considerations in place.

Melhores práticas de índiceIndex Best Practices

Para melhorar o desempenho da instrução MERGE, é recomendável seguir estas diretrizes de índice:To improve the performance of the MERGE statement, we recommend the following index guidelines:

  • Crie um índice com base nas colunas de junção da tabela de origem que seja exclusivo e abrangente.Create an index on the join columns in the source table that is unique and covering.
  • Crie um índice clusterizado exclusivo com base nas colunas de junção da tabela de destino.Create a unique clustered index on the join columns in the target table.

Esses índices asseguram que as chaves de junção sejam exclusivas e que os dados das tabelas sejam classificados.These indexes ensure that the join keys are unique and the data in the tables is sorted. O desempenho das consultas é aprimorado porque o otimizador de consulta não precisa executar processamento de validação extra para localizar e atualizar linhas duplicadas, e não há necessidade de operações de suporte adicionais.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.

Melhores práticas de JOINJOIN Best Practices

Para melhorar o desempenho da instrução MERGE e assegurar a obtenção dos resultados corretos, é recomendável seguir estas diretrizes de junção:To improve the performance of the MERGE statement and ensure correct results are obtained, we recommend the following join guidelines:

  • Especifique apenas condições de pesquisa na cláusula ON <merge_search_condition> que determinem os critérios para corresponder dados nas tabelas de origem e de destino.Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. Ou seja, especifique apenas as colunas da tabela de destino que são comparadas com as colunas correspondentes da tabela de origem.That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
  • Não inclua comparações com outros valores, como uma constante.Do not include comparisons to other values such as a constant.

Para filtrar linhas das tabelas de origem ou de destino, use um dos métodos a seguir.To filter out rows from the source or target tables, use one of the following methods.

  • Especifique o critério de pesquisa para filtragem de linhas na cláusula WHEN apropriada.Specify the search condition for row filtering in the appropriate WHEN clause. Por exemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • Defina uma exibição na origem ou no destino que retorne as linhas filtradas e faça referência à exibição como a tabela de origem ou de destino.Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. Se a exibição for definida na tabela de destino, quaisquer ações sobre ela terão que atender às condições para atualização das exibições.If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. Para obter mais informações sobre como atualizar dados usando uma exibição, consulte Modificar dados por meio de uma exibição.For more information about updating data by using a view, see Modifying Data Through a View.
  • Use a cláusula WITH <common table expression> para filtrar linhas das tabelas de origem e de destino.Use the WITH <common table expression> clause to filter out rows from the source or target tables. Esse método é semelhante a especificar critérios adicionais de pesquisa na cláusula ON e pode produzir resultados incorretos.This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. É recomendável evitar o uso desse método ou testá-lo criteriosamente antes de implementá-lo.We recommend that you avoid using this method or test thoroughly before implementing it.

A operação de junção na instrução MERGE é otimizada da mesma forma que uma junção na instrução SELECT.The join operation in the MERGE statement is optimized in the same way as a join in a SELECT statement. Ou seja, quando o SQL Server processa junções, o otimizador de consulta escolhe o método mais eficaz (entre várias possibilidades) de processamento da junção.That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. Quando a origem e o destino têm tamanho semelhante e as diretrizes de índice descritas anteriormente são aplicadas às tabelas de origem e de destino, um operador merge join é o plano de consulta mais eficiente.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. Isso porque ambas as tabelas são examinadas uma vez e não há necessidade de classificar os dados.This is because both tables are scanned once and there is no need to sort the data. Quando a tabela de origem é menor do que a de destino, é preferível usar um operador nested loops.When the source is smaller than the target table, a nested loops operator is preferable.

Você pode forçar o uso de determinada junção especificando a cláusula OPTION (<query_hint>) na instrução MERGE.You can force the use of a specific join by specifying the OPTION (<query_hint>) clause in the MERGE statement. É recomendável não usar a junção hash como dica de consulta para instruções MERGE, pois esse tipo de junção não usa índices.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.

Melhores práticas de parametrizaçãoParameterization Best Practices

Se uma instrução SELECT, INSERT, UPDATE ou DELETE for executada sem parâmetros, o otimizador de consulta do SQL Server poderá parametrizar a instrução internamente.If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally. Isso significa que qualquer valor literal contido na consulta é substituído por parâmetros.This means that any literal values that are contained in the query are substituted with parameters. Por exemplo, a instrução INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), pode ser implementada internamente como 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). Esse processo, chamado de parametrização simples, aumenta a capacidade do mecanismo relacional de comparar as novas instruções SQL com planos de execução existentes anteriormente compilados.This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. O desempenho da consulta pode ser melhorado porque a frequência das compilações e recompilações de consulta é reduzida.Query performance may be improved because the frequency of query compilations and recompilations are reduced. O otimizador de consulta não aplica o processo de parametrização simples a instruções MERGE.The query optimizer does not apply the simple parameterization process to MERGE statements. Por isso, as instruções MERGE que contêm valores literais podem não ter um desempenho tão bom quanto as instruções INSERT, UPDATE ou DELETE individuais, pois um novo plano é compilado sempre que a instrução MERGE é executada.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.

Para melhorar o desempenho da consulta, é recomendável seguir estas diretrizes de parametrização:To improve query performance, we recommend the following parameterization guidelines:

  • Parametrize todos os valores literais na cláusula ON <merge_search_condition> e nas cláusulas WHEN da instrução MERGE.Parameterize all literal values in the ON <merge_search_condition> clause and in the the WHEN clauses of the MERGE statement. Por exemplo, você pode inserir a instrução MERGE a um procedimento armazenado que substitua os valores literais por parâmetros de entrada apropriados.For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.
  • Se você não conseguir parametrizar a instrução, crie um guia de plano do tipo TEMPLATE e especifique a dica de consulta PARAMETERIZATION FORCED no guia de plano.If you cannot parameterize the statement, create a plan guide of type TEMPLATE and specify the PARAMETERIZATION FORCED query hint in the plan guide.
  • Se instruções MERGE forem executadas com frequência no banco de dados, considere definir a opção PARAMETERIZATION no banco de dados como FORCED.If MERGE statements are executed frequently on the database, consider setting the PARAMETERIZATION option on the database to FORCED. Tome cuidado quando for definir essa opção.Use caution when setting this option. A opção PARAMETERIZATION é uma configuração de banco de dados e afeta a maneira como são processadas todas as consultas feitas nele.The PARAMETERIZATION option is a database-level setting and affects how all queries against the database are processed.

Melhores práticas da cláusula TOPTOP Clause Best Practices

Na instrução MERGE, a cláusula TOP especifica o número ou a porcentagem de linhas que são afetadas depois que as tabelas de origem e de destino são unidas e que as linhas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas.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. A cláusula TOP ainda reduz o número de linhas unidas para o valor especificado e as ações de inserção, atualização ou exclusão são aplicadas às linhas unidas restantes de uma forma não ordenada.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. Ou seja, não há nenhuma ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN.That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. Por exemplo, especificar TOP (10) afeta dez linhas. Dessas linhas, sete podem ser atualizadas e três inseridas, ou uma pode ser excluída, cinco atualizadas e quatro inseridas etc.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.

É comum usar a cláusula TOP para executar operações DML (linguagem de manipulação de dados) em uma tabela grande em lotes.It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches. Quando se usa a cláusula TOP na instrução MERGE para esta finalidade, é importante entender as implicações a seguir.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • O desempenho de E/S poderá ser afetado.I/O performance may be affected.

    A instrução MERGE faz uma verificação completa das tabelas de origem e de destino.The MERGE statement performs a full table scan of both the source and target tables. Dividir a operação em lotes diminui o número de operações de gravação executadas por lote; no entanto, cada lote fará uma verificação completa das tabelas de origem e de destino.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. A atividade de leitura resultante poderá afetar o desempenho da consulta.The resulting read activity may affect the performance of the query.

  • Pode haver resultados incorretos.Incorrect results can occur.

    É importante assegurar que todos os lotes em sucessão visem as novas linhas; do contrário, poderão ocorrer comportamentos indesejados, como inserção incorreta de linhas duplicadas na tabela de destino.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. Isso pode acontecer quando a tabela de origem inclui uma linha que não estava em um lote de destino, mas que estava na tabela de destino global.This can happen when the source table includes a row that was not in a target batch but was in the overall target table.

  • Para assegurar resultados corretos:To insure correct results:

    • Use a cláusula ON para determinar quais linhas de origem afetam linhas de destino existentes e quais são genuinamente novas.Use the ON clause to determine which source rows affect existing target rows and which are genuinely new.
    • Use uma condição adicional na cláusula WHEN MATCHED para determinar se a linha de destino já foi atualizada por um lote anterior.Use an additional condition in the WHEN MATCHED clause to determine if the target row has already been updated by a previous batch.

Como a cláusula TOP só é aplicada depois que essas cláusulas são aplicadas, cada execução insere uma linha genuinamente não correspondente ou atualiza uma linha existente.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.

Melhores práticas de carregamento em massaBulk Load Best Practices

A instrução MERGE pode ser usada para carregar com eficiência dados de carregamento em massa de um arquivo de dados de origem em uma tabela de destino especificando-se a cláusula OPENROWSET(BULK…) como tabela de origem.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. Dessa forma, o arquivo inteiro é processado em um único lote.By doing so, the entire file is processed in a single batch.

Para melhorar o desempenho do processo de mesclagem em lote, é recomendável seguir estas diretrizes:To improve the performance of the bulk merge process, we recommend the following guidelines:

  • Crie um índice clusterizado com base nas colunas de junção da tabela de destino.Create a clustered index on the join columns in the target table.

  • Use as dicas ORDER e UNIQUE na cláusula OPENROWSET(BULK…) para especificar como o arquivo de dados de origem deverá ser classificado.Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

    Por padrão, a operação em massa presume que o arquivo de dados não está ordenado.By default, the bulk operation assumes the data file is unordered. Por isso, é importante que os dados de origem sejam classificados de acordo com o índice clusterizado na tabela de destino e que a dica ORDER seja usada para indicar a ordem, de modo que o otimizador de consulta possa gerar um plano de consulta mais eficaz.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. As dicas são validadas em tempo de execução; se o fluxo de dados não estiver de acordo com as dicas especificadas, ocorrerá um erro.Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.

Essas diretrizes asseguram que as chaves de junção sejam exclusivas e que a ordem de classificação dos dados do arquivo de origem corresponda à da tabela de destino.These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. O desempenho das consultas é aprimorado porque não há necessidade de executar operações de classificação adicionais e não são exigidas cópias de dados desnecessárias.Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required.

Avaliação e diagnóstico do desempenho de MERGEMeasuring and Diagnosing MERGE Performance

Os recursos a seguir estão disponíveis para ajudar você a avaliar e diagnosticar o desempenho de instruções MERGE.The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • Use o contador merge stmt na exibição de gerenciamento dinâmico sys.dm_exec_query_optimizer_info para retornar o número de otimizações de consulta relativas a instruções 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.
  • Use o atributo merge_action_type na exibição de gerenciamento dinâmico sys.dm_exec_plan_attributes para retornar o tipo de plano de execução de gatilho usado como resultado de uma instrução 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.
  • Use o Rastreamento do SQL para coletar dados de solução de problemas para a instrução MERGE da mesma forma que você faria para outras instruções 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. Para obter mais informações, consulte SQL Trace.For more information, see SQL Trace.

ExemplosExamples

A.A. Usar MERGE para executar operações INSERT e UPDATE em uma tabela em uma única instruçãoUsing MERGE to do INSERT and UPDATE operations on a table in a single statement

Um cenário comum é atualizar uma ou mais colunas em uma tabela se existir uma linha correspondente.A common scenario is updating one or more columns in a table if a matching row exists. Ou inserir os dados como uma nova linha se uma linha correspondente não existir.Or, inserting the data as a new row if a matching row doesn't exist. Normalmente, seja qual for o cenário, você transmite parâmetros para um procedimento armazenado que contém as instruções UPDATE e INSERT apropriadas.You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. Com a instrução MERGE, você pode executar as duas tarefas em uma única instrução.With the MERGE statement, you can do both tasks in a single statement. O exemplo a seguir mostra um procedimento armazenado no banco de dados AdventureWorks2012AdventureWorks2012 que contém as instruções INSERT e UPDATE.The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. O procedimento é então modificado para executar as operações equivalentes usando uma única instrução 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. Usar MERGE para executar as operações UPDATE e DELETE em uma tabela em uma única instruçãoUsing MERGE to do UPDATE and DELETE operations on a table in a single statement

O exemplo a seguir usa MERGE para atualizar diariamente a tabela ProductInventory no banco de dados de exemplo AdventureWorks2012AdventureWorks2012 com base em pedidos processados na tabela 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. A coluna Quantity da tabela ProductInventory foi atualizada subtraindo o número de pedidos colocados a cada dia para cada produto na tabela 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. Se o número de pedidos de um produto reduzir o nível de estoque de um produto para 0 ou menos, a linha desse produto será excluída da tabela 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. Usar MERGE para executar as operações UPDATE e INSERT em uma tabela de destino usando uma tabela de origem derivadaUsing MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

O exemplo a seguir usa MERGE para modificar a tabela SalesReason no banco de dados AdventureWorks2012AdventureWorks2012 atualizando ou inserindo linhas.The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. Quando o valor de NewName na tabela de origem corresponde a um valor na coluna Name da tabela de destino (SalesReason), a coluna ReasonType é atualizada na tabela de destino.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. Quando o valor de NewName não corresponde, a linha de origem é inserida na tabela de destino.When the value of NewName doesn't match, the source row is inserted into the target table. A tabela de origem é uma tabela derivada que usa o construtor de valor de tabela do Transact-SQLTransact-SQL para especificar várias linhas para a tabela de origem.The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. Para obter mais informações sobre como usar o construtor de valor de tabela em uma tabela derivada, veja Construtor de valor de tabela (Transact-SQL).For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). O exemplo também mostra como armazenar os resultados da cláusula OUTPUT em uma variável de tabela.The example also shows how to store the results of the OUTPUT clause in a table variable. E, em seguida, você resume os resultados da instrução MERGE executando uma operação de seleção simples que retorna a contagem de linhas inseridas e atualizadas.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. Inserindo os resultados da instrução MERGE em outra tabelaInserting the results of the MERGE statement into another table

O exemplo a seguir captura dados retornados da cláusula OUTPUT de uma instrução MERGE e insere esses dados em outra tabela.The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. A instrução MERGE atualiza diariamente a coluna Quantity da tabela ProductInventory no banco de dados AdventureWorks2012AdventureWorks2012 com base em pedidos processados na tabela 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. O exemplo captura as linhas atualizadas e as insere em outra tabela que é usada para rastrear as alterações do estoque.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. Usar MERGE para executar INSERT ou UPDATE em uma tabela de borda de destino em um banco de dados de grafoUsing MERGE to do INSERT or UPDATE on a target edge table in a graph database

Neste exemplo, você cria tabelas de nó Person e City e uma tabela de borda livesIn.In this example, you create node tables Person and City and an edge table livesIn. Você usa a instrução MERGE na borda livesIn para inserir uma nova linha se a borda ainda não existir entre um Person e City.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. Se a borda já existir, você apenas atualizará o atributo StreetAddress na borda 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

Consulte TambémSee Also