MERGE (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pool SQL dedicado apenas)Banco de dados SQL no Microsoft FabricWarehouse no Microsoft Fabric

A MERGE instrução executa operações de inserção, atualização ou exclusão em uma tabela de destino dos resultados de uma junção com uma tabela de origem. Por exemplo, sincronize duas tabelas inserindo, atualizando ou excluindo linhas em uma tabela com base nas diferenças encontradas na outra tabela.

Este artigo fornece sintaxe, argumentos, comentários, permissões e exemplos diferentes com base na versão do produto selecionada. Selecione a versão do produto desejada na lista suspensa de versão.

Convenções de sintaxe de Transact-SQL

Syntax

Sintaxe do SQL Server e do Banco de Dados SQL do Azure:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    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 . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

Arguments

COM <common_table_expression>

Especifica o conjunto de resultados nomeado temporário ou a exibição, também conhecida como expressão de tabela comum, que é definida dentro do escopo da MERGE instrução. O conjunto de resultados deriva de uma consulta simples e é referenciado pela instrução MERGE . Para obter mais informações, confira WITH common_table_expression (Transact-SQL).

TOP ( expressão ) [ PERCENT ]

Especifica o número ou a porcentagem de linhas afetadas. expression pode ser um número ou um percentual das linhas. As linhas referenciadas na TOP expressão não são organizadas em nenhuma ordem. Para saber mais, confira TOP (Transact-SQL).

A TOP cláusula se aplica depois que toda a tabela de origem e toda a junção da tabela de destino e as linhas unidas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas. A TOP cláusula reduz ainda mais o número de linhas unidas ao valor especificado. Essas ações (inserir, atualizar ou excluir) aplicam-se às linhas restantes unidas de maneira não ordenada. Ou seja, não há nenhuma ordem na qual as linhas são distribuídas entre as ações definidas nas WHEN cláusulas. Por exemplo, especificar TOP (10) afeta 10 linhas. Dessas linhas, sete podem ser atualizadas e três inseridas, ou uma pode ser excluída, cinco atualizadas e quatro inseridas, e assim por diante.

Sem filtros na tabela de origem, a MERGE instrução pode executar uma verificação de tabela ou uma verificação de índice clusterizado na tabela de origem, bem como uma verificação de tabela ou uma verificação de índice clusterizada da tabela de destino. Portanto, o desempenho de E/S às vezes é afetado mesmo ao usar a TOP cláusula para modificar uma tabela grande criando vários lotes. Nesse cenário, é importante garantir que todos os lotes sucessivos se destinem a novas linhas.

database_name

O nome do banco de dados no qual target_table está localizado.

schema_name

O nome do esquema ao qual target_table pertence.

target_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>. target_table é o destino de qualquer operação de inserção, atualização ou exclusão especificada pelas WHEN cláusulas da MERGE instrução.

Se target_table for uma exibição, qualquer ação com ela deverá atender às condições para atualizar exibições. Para obter mais informações, confira Modificar dados por meio de uma exibição.

target_table não pode ser uma tabela remota. target_table não pode ter regras definidas. target_table não pode ser uma tabela com otimização de memória.

As dicas podem ser especificadas como um <merge_hint>.

[ COMO ] table_alias

Um nome alternativo para fazer referência a uma tabela para a target_table.

USANDO <table_source>

Especifica a fonte de dados que corresponde às linhas de dados em target_table com base em <merge_search_condition>. O resultado dessa correspondência determina as ações a serem tomadas pelas WHEN cláusulas da MERGE instrução. <table_source> pode ser uma tabela remota ou uma tabela derivada que acessa tabelas remotas.

<table_source> pode ser uma tabela derivada que usa o construtor de valor de tabela do Transact-SQL para criar uma tabela especificando várias linhas.

[ COMO ] table_alias

Um nome alternativo para fazer referência a uma tabela para a table_source.

Para obter mais informações sobre a sintaxe e os argumentos dessa cláusula, confira FROM (Transact-SQL).

EM <merge_search_condition>

Especifica as condições nas quais <table_source> se une a target_table para determinar em que pontos há correspondências.

Caution

É importante especificar apenas as colunas da tabela de destino a serem usadas para fins de correspondência. Ou seja, especifique as colunas da tabela de destino que são comparadas à coluna correspondente da tabela de origem. Não tente melhorar o desempenho da consulta filtrando linhas na tabela de destino na ON cláusula; por exemplo, como especificar AND NOT target_table.column_x = value. Isso pode retornar resultados inesperados e incorretos.

QUANDO CORRESPONDENTE, MERGE_MATCHED <>

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

A MERGE instrução pode ter, no máximo, duas WHEN MATCHED cláusulas. Se duas cláusulas forem especificadas, a primeira cláusula deverá ser acompanhada por uma AND<search_condition> cláusula. Para qualquer linha específica, a segunda WHEN MATCHED cláusula só será aplicada se a primeira não for. Se houver duas WHEN MATCHED cláusulas, uma deverá especificar uma UPDATE ação e outra deve especificar uma DELETE ação. Quando UPDATE é especificado na cláusula e mais de uma linha corresponde <merge_matched> a uma linha em <table_source> com base em, o <merge_search_condition> SQL Server retorna um erro. A MERGE instrução não pode atualizar a mesma linha mais de uma vez ou atualizar e excluir a mesma linha.

QUANDO NÃO FOR CORRESPONDIDO [ POR DESTINO ] EM SEGUIDA <, merge_not_matched>

Especifica que uma linha é inserida em target_table para cada linha retornada que <table_source> ON <merge_search_condition> não corresponde a uma linha em target_table, mas atende a uma condição de pesquisa adicional, se presente. Os valores a serem inseridos são especificados pela cláusula <merge_not_matched>. A MERGE instrução pode ter apenas uma WHEN NOT MATCHED [ BY TARGET ] cláusula.

QUANDO NÃO FOR CORRESPONDIDO PELA ORIGEM, MERGE_MATCHED <>

Especifica que todas as linhas de *target_table, que não correspondem às linhas retornadas e <table_source> ON <merge_search_condition>que atendem a qualquer condição de pesquisa adicional, são atualizadas ou excluídas de acordo com a <merge_matched> cláusula.

A MERGE instrução pode ter no máximo duas WHEN NOT MATCHED BY SOURCE cláusulas. Se duas cláusulas forem especificadas, a primeira cláusula deverá ser acompanhada por uma AND<clause_search_condition> cláusula. Para qualquer linha específica, a segunda WHEN NOT MATCHED BY SOURCE cláusula só será aplicada se a primeira não for. Se houver duas WHEN NOT MATCHED BY SOURCE cláusulas, uma deverá especificar uma UPDATE ação e outra deve especificar uma DELETE ação. Somente as colunas da tabela de destino podem ser referenciadas em <clause_search_condition>.

Quando nenhuma linha é retornada por <table_source>, não é possível acessar as colunas na tabela de origem. 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. Por exemplo, a cláusula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 pode fazer com que a instrução falhe porque não é possível acessar Col1 na tabela de origem.

E <clause_search_condition>

Especifica qualquer critério de pesquisa válido. Para obter mais informações, consulte a condição de pesquisa (Transact-SQL).

<table_hint_limited>

Especifica uma ou mais dicas de tabela a serem aplicadas na tabela de destino para cada uma das ações de inserção, atualização ou exclusão feitas pela instrução MERGE . A WITH palavra-chave e os parênteses são necessários.

NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, consulte dicas de tabela (Transact-SQL).

Especificar a TABLOCK dica em uma tabela que é o destino de uma instrução INSERT tem o mesmo efeito que especificar a TABLOCKX dica. Um bloqueio exclusivo é obtido na tabela. Quando FORCESEEK é especificada, ela se aplica a uma instância implícita da tabela de destino unida à tabela de origem.

Caution

Especificar READPAST com WHEN NOT MATCHED [ BY TARGET ] THEN INSERT pode resultar em INSERT operações que violam UNIQUE restrições.

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. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

<output_clause>

Retorna uma linha para cada linha de target_table que é atualizada, inserida ou excluída, sem nenhuma ordem específica. $action pode ser especificado na cláusula de saída. $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. A OUTPUT cláusula é a maneira recomendada de consultar ou contar linhas afetadas por um MERGE. Para obter mais informações sobre os argumentos e o comportamento dessa cláusula, consulte a cláusula OUTPUT (Transact-SQL).

OPTION ( <query_hint> [ ,... n ] )

Especifica que dicas de otimização são usadas para personalizar a maneira como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

<merge_matched>

Especifica a ação de atualização ou exclusão aplicada a todas as linhas de target_table que não correspondem às linhas retornadas e <table_source> ON <merge_search_condition>que atendem a qualquer condição de pesquisa adicional.

SET_CLAUSE DO CONJUNTO <DE ATUALIZAÇÕES>

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.

Para obter mais informações sobre os argumentos dessa cláusula, confira UPDATE (Transact-SQL). Não é permitido definir uma variável com o mesmo valor de uma coluna.

DELETE

Especifica que as linhas que correspondem a linhas em target_table são excluídas.

<merge_not_matched>

Especifica os valores a serem inseridos na tabela de destino.

( column_list )

Uma lista de uma ou mais colunas da tabela de destino na qual inserir dados. As colunas devem ser especificadas como um nome de única parte ou então a MERGE instrução falhará. column_list deve ser colocada entre parênteses e separada por vírgulas.

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. As expressões não podem conter uma instrução EXECUTE .

VALORES PADRÃO

Força a linha inserida a conter os valores padrão definidos para cada coluna.

Para obter mais informações sobre essa cláusula, veja INSERT (Transact-SQL).

<search_condition>

Especifica os critérios de pesquisa para especificar <merge_search_condition> ou <clause_search_condition>. Para obter mais informações sobre os argumentos para essa cláusula, consulte a condição de pesquisa (Transact-SQL).

<padrão de pesquisa de grafo>

Especifica o padrão de correspondência do grafo. Para obter mais informações sobre os argumentos dessa cláusula, confira MATCH (Transact-SQL).

Remarks

O comportamento condicional descrito para a instrução MERGE funciona melhor quando as duas tabelas têm uma mistura complexa de características correspondentes. Por exemplo, inserindo uma linha se ela não existir ou atualizando uma linha se ela tiver correspondência. Ao simplesmente atualizar uma tabela com base nas linhas de outra tabela, melhore o desempenho e a escalabilidade com INSERTinstruções e UPDATE instruçõesDELETE. Por exemplo:

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

Pelo menos uma das três MATCHED cláusulas deve ser especificada, mas elas podem ser especificadas em qualquer ordem. Uma variável não pode ser atualizada mais de uma vez na mesma MATCHED cláusula.

Qualquer ação de inserção, atualização ou exclusão especificada na tabela de destino pela MERGE instrução é limitada por quaisquer restrições definidas nela, incluindo quaisquer restrições de integridade referencial em cascata. Se IGNORE_DUP_KEY for ON para índices exclusivos na tabela de destino, MERGE ignorará essa configuração.

A MERGE instrução requer um ponto-e-vírgula (;) como terminador de instrução. O erro 10713 é gerado quando uma MERGE instrução é executada sem o terminador.

Quando usado após MERGE, @@ROWCOUNT (Transact-SQL) retorna o número total de linhas inseridas, atualizadas e excluídas para o cliente.

MERGE é uma palavra-chave totalmente reservada quando o nível de compatibilidade do banco de dados é definido 100 como ou superior. A MERGE instrução está disponível nos níveis de compatibilidade de banco de dados e 90 em ambos100; no entanto, a palavra-chave não é totalmente reservada quando o nível de compatibilidade do banco de dados é definido como 90.

Caution

Não use a instrução MERGE ao usar a replicação de atualização na fila. O MERGE gatilho de atualização e enfileirado não são compatíveis. Substitua a MERGE instrução por uma instrução INSERT e UPDATE instruções.

Implementação do gatilho

Para cada ação de inserção, atualização ou exclusão especificada na instrução, o MERGE SQL Server aciona todos os gatilhos correspondentes AFTER definidos na tabela de destino, mas não garante em qual ação disparar gatilhos primeiro ou último. Os gatilhos definidos para a mesma ação respeitam a ordem que você especifica. Para obter mais informações sobre a configuração da ordem de acionamento do gatilho, veja Especificar o primeiro e o último gatilho.

Se a tabela de destino tiver um gatilho OF habilitado INSTEAD definido para uma ação de inserção, atualização ou exclusão feita por uma MERGE instrução, ela deverá ter um gatilho OF habilitado INSTEAD para todas as ações especificadas na MERGE instrução.

Se algum INSTEAD gatilho OF UPDATE ou INSTEAD OF DELETE for definido em target_table, as operações de atualização ou exclusão não serão executadas. Em vez disso, os gatilhos serão disparados e as tabelas inserted e deleted serão populadas adequadamente.

Se algum INSTEAD gatilho OF INSERT for definido em target_table, a operação de inserção não será executada. Em vez disso, a tabela será populada adequadamente.

Note

Ao contrário de instruções e INSERT instruções separadasUPDATEDELETE, o número de linhas refletidas por @@ROWCOUNT dentro de um gatilho pode ser maior. O @@ROWCOUNT gatilho interno AFTER (independentemente das instruções de modificação de dados que o gatilho captura) refletirá o número total de linhas afetadas pelo MERGE. Por exemplo, se uma MERGE instrução inserir uma linha, atualizar uma linha e excluir uma linha, @@ROWCOUNT será três para qualquer AFTER gatilho, mesmo que o gatilho seja declarado apenas para INSERT instruções.

Permissions

SELECT Requer permissão na tabela de origem e INSERT, UPDATEou DELETE permissões na tabela de destino. Para obter mais informações, consulte a seção Permissões nos artigos SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL)e DELETE (Transact-SQL).

Práticas recomendadas de índice

Usando a MERGE instrução, você pode substituir as instruções DML individuais por uma única instrução. Isso melhora o desempenho de consultas porque as operações são executadas em uma única instrução, diminuindo assim o número de vezes em que os dados das tabelas de origem e de destino são processados. Todavia, os ganhos de desempenho dependem de você ter os índices e junções corretos e de outras considerações.

Para melhorar o desempenho da MERGE instrução, recomendamos as seguintes diretrizes de índice:

• Crie índices para facilitar a junção entre a origem e o MERGEdestino do :
  • Crie um índice nas colunas de junção da tabela de origem com chaves que incluem a lógica de junção para a tabela de destino. Ele deverá ser exclusivo se possível.
  • Além disso, crie um índice nas colunas de junção da tabela de destino. Ele deverá ser um índice clusterizado exclusivo se possível.
  • Esses dois índices garantem que os dados sejam classificados nas tabelas. Além disso, a exclusividade auxilia o desempenho da comparação. 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.
• Evite tabelas com qualquer forma de índice columnstore como o destino das MERGE instruções. Assim como acontece com qualquer UPDATEs, você pode encontrar melhor desempenho com índices columnstore atualizando uma tabela rowstore preparada e executando um lote DELETE e INSERT, em vez de um UPDATE ou MERGE.

Considerações sobre a simultaneidade da instrução MERGE

Em termos de bloqueio, MERGE é diferente das instruções, consecutivas INSERTUPDATEe DELETE discretas. MERGE ainda executa INSERT, UPDATEe DELETE operações, no entanto, usando mecanismos de bloqueio diferentes. Pode ser mais eficiente gravar instruções e INSERT instruções discretas UPDATEDELETEpara algumas necessidades do aplicativo. Em escala, MERGE pode introduzir problemas de simultaneidade complicados ou exigir solução de problemas avançada. Dessa forma, planeje testar completamente qualquer MERGE instrução antes de implantar na produção.

MERGEas instruções são uma substituição adequada para operações discretas INSERTUPDATEe DELETE em (mas não limitadas a) nos seguintes cenários:

• As operações ETL que envolvem grandes contagens de linhas são executadas durante um período em que não* há expectativa de outras operações simultâneas. Quando a simultaneidade pesada é esperada, separada INSERT, UPDATEe DELETE a lógica pode ter um desempenho melhor, com menos bloqueio, do que uma MERGE instrução.
• É improvável executar por muito tempo operações complexas que envolvem pequenas contagens de linhas e transações.
• Operações complexas que envolvem tabelas de usuário em que é possível projetar índices para garantir planos de execução ideais, evitando executar verificações e pesquisas de tabela em benefício de verificações de índice ou de preferência buscas de índice.

Outras considerações sobre a simultaneidade:

• Em alguns cenários em que se espera que as chaves exclusivas sejam inseridas e atualizadas pelo MERGE, especificando a HOLDLOCK opção impedirá violações de chave exclusivas. HOLDLOCK é um sinônimo para o nível de isolamento da SERIALIZABLE transação, que não permite que outras transações simultâneas modifiquem os dados lidos por essa transação. SERIALIZABLE é o nível de isolamento mais seguro, mas fornece a menor simultaneidade com outras transações que retém bloqueios em intervalos de dados para impedir que linhas fantasmas sejam inseridas ou atualizadas enquanto as leituras estão em andamento. Para obter mais informações sobre HOLDLOCK, consulte Dicas de Tabela e SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Práticas recomendadas de JOIN

Para melhorar o desempenho da instrução e garantir que os MERGE resultados corretos sejam obtidos, recomendamos as seguintes diretrizes de junção:

• Especifique apenas as condições de pesquisa na ON <merge_search_condition> cláusula que determinam os critérios para correspondência de dados nas tabelas de origem e de destino. Isto é, especifique apenas as colunas da tabela de destino que são comparadas com as colunas correspondentes da tabela de origem.
• Não inclua comparações com outros valores, como uma constante.

Para filtrar linhas das tabelas de origem ou de destino, use um dos métodos a seguir.

• Especifique a condição de pesquisa para filtragem de linha na cláusula apropriada WHEN . Por exemplo, 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. Se a exibição for definida na tabela de destino, quaisquer ações sobre ela deverão atender às condições para atualização das exibições. Para obter mais informações sobre como atualizar dados usando uma exibição, consulte Modificar Dados por meio de uma exibição.
• Use a cláusula WITH <common table expression> para filtrar linhas das tabelas de origem e de destino. Esse método é semelhante à especificação de critérios de pesquisa adicionais na ON cláusula e pode produzir resultados incorretos. É recomendável evitar o uso desse método ou testá-lo criteriosamente antes de implementá-lo.

A operação de junção na instrução MERGE é otimizada da mesma forma que uma junção em uma SELECT instrução. 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. 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. Isso porque ambas as tabelas são examinadas uma vez e não há necessidade de classificar os dados. Quando a tabela de origem é menor do que a de destino, é preferível usar um operador nested loops.

Você pode forçar o uso de uma junção específica especificando a OPTION (<query_hint>) cláusula na MERGE instrução. Recomendamos que você não use a junção de hash como uma dica de consulta para MERGE instruções porque esse tipo de junção não usa índices.

Práticas recomendadas de parametrização

Se uma instrução ou SELECTINSERTUPDATE instrução DELETEfor executada sem parâmetros, o otimizador de consulta do SQL Server poderá optar por parametrizar a instrução internamente. Isso significa que qualquer valor literal contido na consulta é substituído por parâmetros. Por exemplo, a instrução INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) poderá ser implantada internamente como INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Esse processo, que é chamado de parametrização simples, aumenta a capacidade do mecanismo relacional de fazer a correspondência das novas instruções SQL com planos de execução existentes anteriormente compilados. O desempenho da consulta pode ser melhorado porque a frequência das compilações e recompilações de consulta é reduzida. O otimizador de consulta não aplica o processo de parametrização simples a MERGE instruções. Portanto, MERGE instruções que contêm valores literais podem não ser executadas e individuais INSERT, UPDATEou DELETE instruções, porque um novo plano é compilado sempre que a MERGE instrução é executada.

Para melhorar o desempenho da consulta, é recomendável seguir estas diretrizes de parametrização:

• Parametrize todos os valores literais na ON <merge_search_condition> cláusula e nas WHEN cláusulas da MERGE instrução. Por exemplo, você pode incorporar a MERGE instrução em um procedimento armazenado substituindo os valores literais por parâmetros de entrada apropriados.
• 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. Para obter mais informações, confira Especificar comportamento de parametrização de consulta usando guias de plano.
• Se MERGE as instruções forem executadas com frequência no banco de dados, considere definir a opção PARAMETERIZATION no banco de dados como FORCED. Tome cuidado quando for definir esta opção. A opção PARAMETERIZATION é uma configuração de banco de dados e afeta a maneira como são processadas todas as consultas feitas nele. Para obter mais informações, confira a Parametrização Forçada.
• Como uma alternativa mais nova e fácil aos guias de plano, considere usar uma estratégia semelhante com dicas do Repositório de Consultas. Para obter mais informações, confira as Dicas do Repositório de Consultas.

Melhores práticas da cláusula TOP

MERGE Na instrução, a TOP cláusula especifica o número ou o percentual de linhas que são afetadas após a junção da tabela de origem e da tabela de destino e depois que as linhas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas. A TOP cláusula reduz ainda mais o número de linhas unidas ao valor especificado e as ações de inserção, atualização ou exclusão são aplicadas às linhas unidas restantes de forma não ordenada. Ou seja, não há nenhuma ordem na qual as linhas são distribuídas entre as ações definidas nas WHEN cláusulas. Por exemplo, especificar TOP (10) afeta 10 linhas; dessas linhas, 7 podem ser atualizadas e 3 inseridas ou 1 pode ser excluída, 5 atualizadas e 4 inseridas e assim por diante.

É comum usar a TOP cláusula para executar operações de DML (linguagem de manipulação de dados) em uma tabela grande em lotes. Ao usar a TOP cláusula na MERGE instrução para essa finalidade, é importante entender as implicações a seguir.

• O desempenho de E/S poderá ser afetado.

  A MERGE instrução executa uma verificação de tabela completa das tabelas de origem e de destino. Dividir a operação em lotes reduz o número de operações de gravação executadas por lote; no entanto, cada lote faz uma verificação de tabela completa das tabelas de origem e de destino. A atividade de leitura resultante poderá afetar o desempenho da consulta e de outra atividade simultânea nas tabelas.

• Pode haver resultados incorretos.

  É 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. 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. Para assegurar resultados adequados:

  • Use a ON cláusula para determinar quais linhas de origem afetam as linhas de destino existentes e quais são genuinamente novas.
  • Use uma condição adicional na WHEN MATCHED cláusula para determinar se a linha de destino já foi atualizada por um lote anterior.
  • Use uma condição adicional na WHEN MATCHED cláusula e SET na lógica para verificar se a mesma linha não pode ser atualizada duas vezes.

Como a TOP cláusula só é aplicada depois que essas cláusulas são aplicadas, cada execução insere uma linha genuinamente incompatível ou atualiza uma linha existente.

Melhores práticas de carregamento em massa

A MERGE instrução pode ser usada para carregar dados em massa com eficiência de um arquivo de dados de origem em uma tabela de destino especificando a OPENROWSET(BULK...) cláusula como a fonte da tabela. Dessa forma, o arquivo inteiro é processado em um único lote.

Para melhorar o desempenho do processo de mesclagem em lote, é recomendável seguir estas diretrizes:

• Crie um índice clusterizado com base nas colunas de junção da tabela de destino.

• Desabilite outros índices não exclusivos e não clusterizados na tabela de destino durante a carga MERGEem massa, habilite-os posteriormente. Isso é comum e útil em operações noturnas de carregamento em massa.

• Use as ORDER dicas e UNIQUE dicas na OPENROWSET(BULK...) cláusula para especificar como o arquivo de dados de origem é classificado.

  Por padrão, a operação em massa presume que o arquivo de dados não está ordenado. Portanto, é importante que os dados de origem sejam classificados de acordo com o índice clusterizado na tabela de destino e que a ORDER dica seja usada para indicar a ordem para que o otimizador de consulta possa gerar um plano de consulta mais eficiente. As dicas são validadas em runtime; se o fluxo de dados não estiver de acordo com as dicas especificadas, ocorrerá um erro.

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. 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.

Avalie e diagnostique o desempenho de MERGE

Os recursos a seguir estão disponíveis para ajudá-lo a medir e diagnosticar o desempenho das MERGE instruções.

• Use o contador stmt de mesclagem na exibição de gerenciamento dinâmico sys.dm_exec_query_optimizer_info para retornar o número de otimizações de consulta que são para MERGE instruções.
• Use o merge_action_type atributo 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 MERGE instrução.
• Use uma Sessão de Eventos Estendidos para coletar dados de solução de problemas para a MERGE instrução da mesma forma que faria para outras instruções DML (linguagem de manipulação de dados). Para obter mais informações sobre a visão geral de Eventos Estendidos, confira Início Rápido: Eventos Estendidos e Usar o SSMS XEvent Profiler.

Examples

A. Use MERGE para executar as operações INSERT e UPDATE em uma tabela em uma única instrução

Um cenário comum é atualizar uma ou mais colunas em uma tabela se existir uma linha correspondente. Ou inserir os dados como uma nova linha se uma linha correspondente não existir. Normalmente, você faz qualquer um dos cenários passando parâmetros para um procedimento armazenado que contém as instruções e UPDATE as instruções apropriadasINSERT. Com a instrução MERGE , você pode executar ambas as tarefas em uma única instrução. O exemplo a seguir mostra um procedimento armazenado no banco de dados AdventureWorks2025 que contém tanto uma INSERT instrução quanto uma UPDATE instrução. Em seguida, o procedimento é modificado para executar as operações equivalentes usando uma única MERGE instrução.

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 tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.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. Use MERGE para executar as operações UPDATE e DELETE em uma tabela em uma única instrução

O exemplo a seguir é usado MERGE para atualizar a ProductInventory tabela no banco de dados de exemplos AdventureWorks2025, diariamente, com base nos pedidos processados na SalesOrderDetail tabela. A coluna Quantity da tabela ProductInventory foi atualizada subtraindo o número de pedidos colocados a cada dia para cada produto na tabela SalesOrderDetail. 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.

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. Use MERGE para executar as operações UPDATE e INSERT em uma tabela de destino usando uma tabela de origem derivada

O exemplo a seguir é usado MERGE para modificar a SalesReason tabela no banco de dados AdventureWorks2025, atualizando ou inserindo linhas.

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. Quando o valor de NewName não corresponde, a linha de origem é inserida na tabela de destino. A tabela de origem é uma tabela derivada que usa o construtor de valor de tabela do Transact-SQL para especificar várias linhas para a tabela de origem. 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).

A OUTPUT cláusula pode ser útil para consultar o resultado das MERGE instruções, para obter mais informações, consulte a cláusula OUTPUT (Transact-SQL). O exemplo também mostra como armazenar os resultados da OUTPUT cláusula em uma variável de tabela. Em seguida, você resume os resultados da MERGE instrução executando uma operação de seleção simples que retorna a contagem de linhas inseridas e atualizadas.

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.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. Inserir os resultados da instrução MESCLAR em outra tabela

O exemplo a seguir captura os dados retornados da OUTPUT cláusula de uma MERGE instrução e insere esses dados em outra tabela. A MERGE instrução atualiza a Quantity coluna da ProductInventory tabela no banco de dados AdventureWorks2025, com base nos pedidos processados na SalesOrderDetail tabela. O exemplo captura as linhas atualizadas e as insere em outra tabela que é usada para rastrear as alterações do estoque.

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
        INNER 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. Usar MERGE para executar INSERT ou UPDATE em uma tabela de borda de destino em um banco de dados de grafo

Neste exemplo, você cria tabelas de nó Person e City e uma tabela de borda livesIn. Use a MERGE instrução na livesIn borda e insira uma nova linha se a borda ainda não existir entre a Person e City. Se a borda já existir, você apenas atualizará o atributo StreetAddress na borda livesIn.

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

Conteúdo relacionado

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• ATUALIZAÇÃO (Transact-SQL)
• DELETE (Transact-SQL)
• Cláusula OUTPUT (Transact-SQL)
• MERGE em pacotes do Integration Services
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)
• Construtor de valor de tabela (Transact-SQL)