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

APLICA-SE A: simSQL Server (a partir do 2008) simBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Realiza operações de inserção, atualização ou exclusão em uma tabela de destino com base nos resultados da junção com a tabela de origem.Performs insert, update, or delete operations on a target table based on the results of a join with a source table. Por exemplo, você pode sincronizar duas tabelas inserindo, atualizando ou excluindo linhas em uma tabela com base nas diferenças encontradas na outra tabela.For example, you can 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 a linha se ela corresponder.For example, inserting a row if it does not exist, or updating the row if it does match. Ao simplesmente atualizar uma tabela com base nas linhas de outra tabela, desempenho e escalabilidade aprimorados podem ser obtidos com as instruções INSERT, UPDATE e DELETE básicas.When simply updating one table based on the rows of another table, improved performance and scalability can be achieved 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> ::=  
    { [ NOT ] <predicate> | ( <search_condition> ) }   
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]   
[ ,...n ]   

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

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

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

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

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, definido no escopo da instrução MERGE.Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement. O conjunto de resultados é derivado de uma consulta simples e referenciado pela instrução MERGE.The result set is derived 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 rows that are affected. 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 é aplicada 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 is applied after the entire source table and the entire target table are joined and the joined 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.

Como a instrução MERGE executa um exame completo das tabelas de origem e de destino, o desempenho de E/S pode ser afetado ao usar a cláusula TOP para modificar uma tabela grande criando vários lotes.Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. Neste cenário, é importante garantir que todos os lotes sucessivos se destinem a novas linhas.In this scenario, it is 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.Is the name of the database in which target_table is located.

schema_nameschema_name
É o nome do esquema ao qual a target_table pertence.Is the name of the schema to which target_table belongs.

target_tabletarget_table
É a tabela ou exibição com relação à qual as linhas de dados de <table_source> são correspondidas com base na <clause_search_condition>.Is 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 cannot be a remote table. target_table não pode ter nenhuma regra definida.target_table cannot have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
É um nome alternativo usado para fazer referência a uma tabela.Is an alternative name used to reference a table.

USING <table_source>USING <table_source>
Especifica a fonte de dados que é combinada com as linhas de dados em target_table com base em <merge_search condition>.Specifies the data source that is 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> é unido a target_table para determinar em que pontos há correspondências.Specifies the conditions on which <table_source> is joined with target_table to determine where they match.

Cuidado

É importante especificar apenas as colunas da tabela de destino que são usadas para fins de correspondência.It is important to specify only the columns from the target table that are used 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 uma consulta filtrando linhas na tabela de destino na cláusula ON, por exemplo, especificando AND NOT target_table.column_x = value.Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by 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 da target_table que correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer critério de pesquisa adicional são atualizadas ou excluídas de acordo com a cláusula <merge_matched>.Specifies that all rows of target_table that 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 por uma cláusula AND <search_condition>.If two clauses are specified, then 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 is not. 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, then one must specify an UPDATE action and one must specify a DELETE action. Se 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.If 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 cannot 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 critério 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 does not match a row in target_table, but does satisfy 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 pode ter apenas uma cláusula WHEN NOT MATCHED.The MERGE statement can have only one WHEN NOT MATCHED clause.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
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 critério de pesquisa adicional são atualizadas ou excluídas de acordo com a cláusula <merge_matched>.Specifies that all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that 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 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 is not. 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 cannot 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 que são 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 that are applied on the target table for each of the insert, update, or delete actions that are performed 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 permitidos.NOLOCK and READUNCOMMITTED are not 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 is 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 é aplicada a uma instância implícita da tabela de destino unida à tabela de origem.When FORCESEEK is specified, it is applied 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 identificação de um ou mais índices em uma tabela de destino para a execução de uma junção implícita com a tabela de origem.Specifies the name or ID of one or more indexes on the target table for performing 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 is 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 de 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 that was performed 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 critério de pesquisa adicional.Specifies the update or delete action that is applied to all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition.

UPDATE SET <set_clause>UPDATE SET <set_clause>
Especifica a lista de nomes de colunas ou de variáveis que devem ser atualizados na tabela de destino e os valores que devem ser usados na atualização.Specifies the list of column or variable names to be updated 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 o mesmo valor de uma coluna para uma variável.Setting a variable to the same value as a column is not permitted.

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 os dados devem ser inseridos.Is 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.Is 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 cannot 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 usados para especificar <merge_search_condition> ou <clause_search_condition>.Specifies the search conditions used 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).

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 cannot 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 actions 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 definida como ON em qualquer índice exclusivo na tabela de destino, MERGE ignorará esta configuração.If IGNORE_DUP_KEY is set to 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 is not fully reserved when the database compatibility level is set to 90.

A instrução MERGE não deve ser usada quando a replicação de atualização enfileirada é utilizada.The MERGE statement should not be used 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 are not 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, o 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 does not 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 executada 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 performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

Se houver qualquer gatilho INSTEAD OF UPDATE ou INSTEAD OF DELETE definido na target_table, as operações de atualização ou de exclusão não serão executadas.If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Em vez disso, os gatilhos são disparados e as tabelas inserted e deleted serão preenchidas adequadamente.Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

Se houver qualquer gatilho INSTEAD OF INSERT definido em target_table, a operação de inserção não será executada.If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Em vez disso, os gatilhos são disparados e a tabela inserted será preenchida de maneira correspondente.Instead, the triggers fire and the inserted table is populated 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 obter informações adicionais, veja a seção Permissões nos tópicos SELECT, INSERT, UPDATE e DELETE.For additional information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE topics.

ExemplosExamples

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

Um cenário comum é atualizar uma ou mais colunas em uma tabela, se uma linha correspondente existir, ou inserir os dados como uma nova linha, se uma linha correspondente não existir.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 does not exist. Isto normalmente é feito transmitindo parâmetros para um procedimento armazenado que contém as instruções UPDATE e INSERT apropriadas.This is usually done 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 perform 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. Em seguida, o procedimento é modificado para executar as operações equivalentes usando uma única instrução MERGE.The procedure is then modified to perform 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. Usando MERGE para executar as operações UPDATE e DELETE em uma tabela em uma única instruçãoUsing MERGE to perform 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 da AdventureWorks2012AdventureWorks2012 com base em pedidos processados na tabela SalesOrderDetail.The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012AdventureWorks2012 sample database on a daily basis, 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. Usando MERGE para executar as operações UPDATE e INSERT em uma tabela de destino usando uma tabela de origem derivadaUsing MERGE to perform 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 does not 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 e resumir os resultados da instrução MERGE executando uma operação de seleção simples que retorna a contagem de linhas inseridas e atualizadas.The example also shows how to store the results of the OUTPUT clause in a table variable and then summarize the results of the MERGE statment by performing 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 rows that are updated and inserts them into another table that is 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  

Consulte TambémSee Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
Cláusula OUTPUT (Transact-SQL) OUTPUT Clause (Transact-SQL)
MERGE em pacotes do Integration Services MERGE in Integration Services Packages
FROM (Transact-SQL) FROM (Transact-SQL)
Construtor de valor de tabela (Transact-SQL)Table Value Constructor (Transact-SQL)