MESCLAR (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (apenas pool SQL dedicado)Base de dados SQL no Microsoft FabricArmazém 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 associaçã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, observações, permissões e exemplos diferentes com base na versão do produto selecionada. Selecione a versão desejada do produto na lista suspensa de versão.

Transact-SQL convenções de sintaxe

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 exibição, também conhecido como expressão de tabela comum, que é definido dentro do escopo da MERGE instrução. O conjunto de resultados deriva de uma consulta simples e é referenciado MERGE pela instrução. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

TOPO ( expressão ) [ PERCENTAGEM ]

Especifica o número ou a porcentagem de linhas afetadas. A expressão pode ser um número ou uma porcentagem das linhas. As linhas referenciadas TOP na expressão não estão organizadas em nenhuma ordem. Para obter mais informações, consulte TOP (Transact-SQL).

A TOP cláusula se aplica depois que toda a tabela de origem e toda a tabela de destino se juntam 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 para o valor especificado. Essas ações (inserir, atualizar ou excluir) aplicam-se às linhas unidas restantes de forma não ordenada. Ou seja, não há uma ordem em que as linhas sejam 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.

Sem filtros na tabela de origem, a MERGE instrução pode executar uma verificação de tabela ou de índice clusterizado na tabela de origem, bem como uma verificação de tabela ou de índice clusterizado 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 tenham como destino 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 são correspondidas <table_source> com base em <clause_search_condition>. target_table é o alvo de quaisquer operações de inserção, atualização ou exclusão especificadas pelas WHEN cláusulas da MERGE declaração.

Se target_table for um modo de exibição, quaisquer ações contra ele devem satisfazer as condições para atualizar os modos de exibição. Para obter mais informações, consulte Modificar dados por meio de um modo de exibição.

target_table não pode ser uma mesa remota. target_table não pode ter nenhuma regra definida sobre ele. target_table não pode ser uma tabela com otimização de memória.

As dicas podem ser especificadas como um <merge_hint>arquivo .

[ COMO ] table_alias

Um nome alternativo para fazer referência a uma tabela para o 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 desta partida dita as ações a serem tomadas pelas WHEN cláusulas da MERGE declaraçã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 Transact-SQL para construir uma tabela especificando várias linhas.

[ COMO ] table_alias

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

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

EM <merge_search_condition>

Especifica as condições nas quais <table_source> as associações com target_table para determinar onde elas correspondem.

Caution

É importante especificar apenas as colunas da tabela de destino a serem usadas para fins de correspondência. Ou seja, especifique colunas da tabela de destino que são comparadas com a 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 COMBINADO, ENTÃO <merge_matched>

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

A MERGE declaração pode ter, no máximo, duas WHEN MATCHED cláusulas. Se forem especificadas duas cláusulas, a primeira deve ser acompanhada de uma AND<search_condition> cláusula. Para qualquer linha, a segunda WHEN MATCHED cláusula só é aplicada se a primeira não for. Se houver duas WHEN MATCHED cláusulas, uma deve especificar uma UPDATE ação e outra deve especificar uma DELETE ação. Quando UPDATE é especificado na <merge_matched> cláusula e mais de uma linha corresponde <table_source> a uma linha em target_table com base no <merge_search_condition>, o 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 CORRESPONDIDO [ POR ALVO ] ENTÃO <merge_not_matched>

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

QUANDO NÃO CORRESPONDIDO PELA FONTE, ENTÃO <merge_matched>

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

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

Quando nenhuma linha é retornada pelo <table_source>, as colunas na tabela de origem não podem ser acessadas. Se a ação de atualização ou exclusão especificada na cláusula fizer referência a <merge_matched> colunas na tabela de origem, o erro 207 (Nome da 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 Col1 na tabela de origem está inacessível.

E clause_search_condition <>

Especifica qualquer condição de pesquisa válida. Para obter mais informações, consulte 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 MERGE instrução. A WITH palavra-chave e os parênteses são obrigató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 dica TABLOCK em uma tabela que é o destino de uma INSERT instrução tem o mesmo efeito que especificar a TABLOCKX dica. Um cadeado exclusivo é levado sobre a mesa. Quando FORCESEEK é especificado, ele se aplica à 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 operações que violam INSERTUNIQUE restrições.

ÍNDICE ( index_val [ ,... n ] )

Especifica o nome ou ID de um ou mais índices na tabela de destino para fazer uma associação implícita com a tabela de origem. Para obter mais informações, consulte Sugestões para tabelas (Transact-SQL).

<output_clause>

Retorna uma linha para cada linha no target_table que é atualizada, inserida ou excluída, sem 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 nessa linha. A OUTPUT cláusula é a maneira recomendada de consultar ou contar linhas afetadas por um MERGEarquivo . Para obter mais informações sobre os argumentos e o comportamento dessa cláusula, consulte Cláusula OUTPUT (Transact-SQL).

OPÇÃO ( <query_hint> [ ,... n ] )

Especifica que as dicas do otimizador 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 pelo <table_source> ON <merge_search_condition>, e que satisfazem qualquer condição de pesquisa adicional.

CONJUNTO DE <ATUALIZAÇÕES set_clause>

Especifica a lista de nomes de colunas ou variáveis a serem atualizados na tabela de destino e os valores com os quais atualizá-los.

Para obter mais informações sobre os argumentos desta cláusula, consulte UPDATE (Transact-SQL). Não há suporte para a definição de uma variável com o mesmo valor de uma coluna.

DELETE

Especifica que as linhas correspondentes às 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 nas quais inserir dados. As colunas devem ser especificadas como um nome de parte única, caso contrário, a MERGE instrução falhará. column_list devem ser colocados entre parênteses e delimitados por vírgulas.

VALORES ( values_list )

Uma lista separada por vírgulas de constantes, variáveis ou expressões que retornam valores para inserir na tabela de destino. As expressões não podem conter uma EXECUTE instrução.

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, consulte INSERT (Transact-SQL).

<search_condition>

Especifica as condições de pesquisa a serem especificadas <merge_search_condition> ou <clause_search_condition>. Para obter mais informações sobre os argumentos para esta cláusula, consulte Condição de pesquisa (Transact-SQL).

<padrão de pesquisa de gráfico>

Especifica o padrão de correspondência do gráfico. Para obter mais informações sobre os argumentos para esta cláusula, consulte MATCH (Transact-SQL).

Remarks

O comportamento condicional descrito para a MERGE instrução funciona melhor quando as duas tabelas têm uma mistura complexa de características correspondentes. Por exemplo, inserir uma linha, se ela não existir, ou atualizar uma linha, se corresponder. Ao simplesmente atualizar uma tabela com base nas linhas de outra tabela, melhore o desempenho e a escalabilidade com INSERT, UPDATEe DELETE instruções. 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 pode ser especificada 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 quaisquer índices exclusivos na tabela de destino, MERGE ignora essa configuração.

A MERGE instrução requer um ponto-e-vírgula (;) como um 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 como 100 ou superior. A MERGE instrução está disponível em ambos os 90 níveis de compatibilidade do 100 banco de dados, 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 ao usar a MERGEreplicação de atualização em fila. O gatilho de atualização e o gatilho MERGE de atualização em fila não são compatíveis. Substitua a MERGE instrução por uma INSERT instrução and UPDATE .

Implementação do gatilho

Para cada ação de inserção, atualização ou exclusão especificada na instrução, o MERGE SQL Server dispara todos os gatilhos correspondentes AFTER definidos na tabela de destino, mas não garante em qual ação disparar os gatilhos primeiro ou por último. Os gatilhos definidos para a mesma ação honram a ordem especificada. Para obter mais informações sobre como definir a ordem de disparo do gatilho, consulte Especificar primeiro e último disparadores.

Se a tabela de destino tiver um gatilho OF habilitado INSTEAD definido nela 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 no target_table, as operações de atualização ou exclusão não serão executadas. Em vez disso, os gatilhos são acionados e as tabelas inseridas e excluídas são preenchidas de acordo.

Se algum INSTEAD gatilho OF INSERT for definido no target_table, a operação de inserção não será executada. Em vez disso, a tabela é preenchida de acordo.

Note

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

Permissions

Requer SELECT 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).

Indexar as melhores práticas

Usando a MERGE instrução, você pode substituir as instruções DML individuais por uma única instrução. Isso pode melhorar o desempenho da consulta porque as operações são executadas dentro de uma única instrução, minimizando o número de vezes que os dados nas tabelas de origem e destino são processados. No entanto, os ganhos de desempenho dependem de ter índices corretos, junções e outras considerações em vigor.

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

• Crie índices para facilitar a junção entre a origem e o destino do MERGE:
  • Crie um índice nas colunas de junção na tabela de origem que tenha chaves cobrindo a lógica de junção para a tabela de destino. Se possível, deve ser exclusivo.
  • Além disso, crie um índice nas colunas de junção na tabela de destino. Se possível, deve ser um índice em cluster exclusivo.
  • Esses dois índices garantem que os dados nas tabelas sejam classificados, e a exclusividade auxilia o desempenho da comparação. O desempenho da consulta é melhorado porque o otimizador de consulta não precisa executar processamento de validação extra para localizar e atualizar linhas duplicadas e operações de classificação adicionais não são necessárias.
• Evite tabelas com qualquer forma de índice columnstore como destino de MERGE instruções. Como acontece com qualquer UPDATEs, você pode encontrar um desempenho melhor com índices columnstore atualizando uma tabela de armazenamento de linhas em estágios e, em seguida, executando um lote DELETE e INSERT, em vez de um UPDATE ou MERGE.

Considerações de simultaneidade para MERGE

Em termos de bloqueio, MERGE é diferente de discreto, consecutivo INSERTUPDATE, e DELETE declarações. MERGE ainda executa INSERT, UPDATEe DELETE operações, porém usando diferentes mecanismos de bloqueio. Pode ser mais eficiente escrever instruções e instruções discretas INSERTUPDATEDELETE para algumas necessidades de aplicativos. Em escala, MERGE pode introduzir problemas complicados de simultaneidade ou exigir solução de problemas avançada. Como tal, planeje testar completamente qualquer MERGE instrução antes de implantar na produção.

MERGE As instruções são um substituto adequado para operações discretas INSERT, UPDATEe DELETE em (mas não limitado a) os seguintes cenários:

• As operações de ETL envolvendo grandes contagens de linhas devem ser executadas durante um período em que outras operações simultâneas não são esperadas*. Quando a simultaneidade pesada é esperada, separar INSERT, UPDATEe DELETE a lógica pode ter um desempenho melhor, com menos bloqueio, do que uma MERGE instrução.
• Operações complexas envolvendo pequenas contagens de linhas e transações improváveis de serem executadas por um período prolongado.
• Operações complexas envolvendo tabelas de usuário onde os índices podem ser projetados para garantir planos de execução ideais, evitando varreduras de tabelas e pesquisas em favor de varreduras de índice ou - idealmente - buscas de índice.

Outras considerações para a simultaneidade:

• Em alguns cenários em que se espera que chaves exclusivas sejam inseridas MERGEe atualizadas pelo , especificar o HOLDLOCK impedirá contra 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 evitar que linhas fantasmas sejam inseridas ou atualizadas enquanto as leituras estão em andamento. Para obter mais informações sobre HOLDLOCKo , consulte Dicas de tabela e SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

ADERIR às melhores práticas

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

• Especifique apenas as condições de ON <merge_search_condition> pesquisa na cláusula que determinam os critérios de correspondência de dados nas tabelas de origem e de destino. Ou seja, 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 seguintes métodos.

• 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 um modo de exibição na origem ou no destino que retorne as linhas filtradas e faça referência ao modo de exibição como a tabela de origem ou destino. Se o modo de exibição for definido na tabela de destino, todas as ações contra ele deverão satisfazer as condições para atualizar os modos de exibição. Para obter mais informações sobre como atualizar dados usando um modo de exibição, consulte Modificar dados por meio de um modo de exibição.
• Use a WITH <common table expression> cláusula para filtrar linhas das tabelas de origem ou de destino. Este método é semelhante à especificação de ON critérios de pesquisa adicionais na cláusula e pode produzir resultados incorretos. Recomendamos que você evite usar esse método ou teste cuidadosamente antes de implementá-lo.

A operação de junção na MERGE instrução é otimizada da mesma forma que uma junção em uma SELECT instrução. Ou seja, quando o SQL Server processa a associação, o otimizador de consulta escolhe o método mais eficiente (entre várias possibilidades) de processar a associação. Quando a origem e o destino são de tamanho semelhante e as diretrizes de índice descritas anteriormente são aplicadas às tabelas de origem e destino, um operador de junção de mesclagem é o plano de consulta mais eficiente. Isso ocorre porque ambas as tabelas são digitalizadas uma vez e não há necessidade de classificar os dados. Quando a origem é menor do que a tabela de destino, um operador de loops aninhados é preferível.

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.

Melhores práticas de parametrização

Se uma SELECTinstrução , INSERT, UPDATE, ou DELETE for executada sem parâmetros, o otimizador de consulta do SQL Server poderá optar por parametrizar a instrução internamente. Isso significa que todos os valores literais contidos na consulta são substituídos por parâmetros. 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). Esse processo, que é chamado de parametrização simples, aumenta a capacidade do mecanismo relacional de corresponder novas instruções SQL com planos de execução existentes e compilados anteriormente. O desempenho da consulta pode ser melhorado porque a frequência de compilações e recompilações de consulta é reduzida. O otimizador de consulta não aplica o processo de parametrização simples às MERGE instruções. Portanto, MERGE instruções que contêm valores literais podem não executar e individual INSERT, UPDATEou DELETE instruções porque um novo plano é compilado cada vez que a MERGE instrução é executada.

Para melhorar o desempenho da consulta, recomendamos as seguintes diretrizes de parametrização:

• Parametrizar todos os valores literais na ON <merge_search_condition> cláusula e nas WHEN cláusulas da MERGE declaraçã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 não for possível parametrizar a instrução, crie um guia de plano do tipo TEMPLATE e especifique a dica PARAMETERIZATION FORCED de consulta no guia de plano. Para obter mais informações, consulte Especificar o comportamento de parametrização da consulta usando guias de plano.
• Se MERGE as instruções forem executadas com freqüência no banco de dados, considere definir a PARAMETERIZATION opção no banco de dados como FORCED. Tenha cuidado ao definir esta opção. A PARAMETERIZATION opção é uma configuração no nível do banco de dados e afeta como todas as consultas no banco de dados são processadas. Para obter mais informações, consulte Parametrização forçada.
• Como uma alternativa mais recente e fácil aos guias de planejamento, considere uma estratégia semelhante com as dicas do Repositório de Consultas. Para obter mais informações, consulte Dicas do Query Store.

Melhores práticas da cláusula TOP

MERGE Na instrução, a TOP cláusula especifica o número ou a porcentagem de linhas que são afetadas depois que a tabela de origem e a tabela de destino são unidas 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 para o 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á uma ordem em que as linhas sejam distribuídas entre as ações definidas nas WHEN cláusulas. Por exemplo, a especificação 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 linguagem de manipulação de dados (DML) em uma tabela grande em lotes. Ao usar a TOP cláusula da MERGE declaração para este fim, é importante entender as seguintes implicações.

• O desempenho de E/S pode ser afetado.

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

• Podem ocorrer resultados incorretos.

  É importante garantir que todos os lotes sucessivos tenham como destino novas linhas ou que possa ocorrer um comportamento indesejado, como a 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 estava na tabela de destino geral. Para garantir resultados corretos:

  • 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 cláusula e WHEN MATCHED na SET 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 incomparável ou atualiza uma linha existente.

Práticas recomendadas de carga em massa

A MERGE instrução pode ser usada para carregar eficientemente dados em massa de um arquivo de dados de origem em uma tabela de destino, especificando a OPENROWSET(BULK...) cláusula como a fonte da tabela. Ao fazer isso, todo o arquivo é processado em um único lote.

Para melhorar o desempenho do processo de mesclagem em massa, recomendamos as seguintes diretrizes:

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

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

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

  Por padrão, a operação em massa pressupõe 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 dica ORDER 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 tempo de execução; Se o fluxo de dados não estiver em conformidade com as dicas especificadas, um erro será gerado.

Essas diretrizes garantem que as chaves de junção sejam exclusivas e que a ordem de classificação dos dados no arquivo de origem corresponda à tabela de destino. O desempenho da consulta é melhorado porque operações de classificação adicionais não são necessárias e cópias de dados desnecessárias não são necessárias.

Medir e diagnosticar o desempenho do MERGE

Os seguintes recursos estão disponíveis para ajudá-lo a medir e diagnosticar o desempenho das MERGE declaraçõ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 para MERGE instruções.
• Use o merge_action_type atributo no modo de 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 estendida 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 visão geral de Eventos Estendidos, consulte Guia de início rápido: eventos estendidos e Usar o SSMS XEvent Profiler.

Examples

A. Use MERGE para executar 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 não existir uma linha correspondente. Normalmente, você faz qualquer um dos cenários passando parâmetros para um procedimento armazenado que contém as instruções e UPDATE apropriadasINSERT. Com a MERGE instrução, você pode fazer ambas as tarefas em uma única instrução. O exemplo seguinte mostra um procedimento armazenado na base de dados AdventureWorks2025 que contém tanto uma INSERT instrução como uma UPDATE instrução. O procedimento é então 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 operações UPDATE e DELETE em uma tabela em uma única instrução

O exemplo seguinte serve MERGE para atualizar a ProductInventory tabela na base de dados de exemplos AdventureWorks2025, diariamente, com base nas encomendas processadas na SalesOrderDetail tabela. A Quantity coluna da ProductInventory tabela é atualizada subtraindo o número de pedidos feitos a cada dia para cada produto na SalesOrderDetail tabela. Se o número de pedidos de um produto cair o nível de estoque de um produto para 0 ou menos, a linha desse produto será excluída da ProductInventory tabela.

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 operações UPDATE e INSERT em uma tabela de destino usando uma tabela de origem derivada

O exemplo seguinte utiliza MERGE a modificação da SalesReason tabela na base de dados AdventureWorks2025, atualizando ou inserindo linhas.

Quando o valor de na tabela de NewName origem corresponde a Name um valor na coluna da tabela de destino, (SalesReason), a ReasonType coluna é atualizada na tabela de destino. Quando o valor de não corresponde, a linha de origem é inserida na tabela de NewName destino. A tabela de origem é uma tabela derivada que usa o construtor de valor de tabela 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, consulte Construtor de valor de tabela (Transact-SQL).

A OUTPUT cláusula pode ser útil para consultar o resultado de MERGE instruções, para obter mais informações, consulte Cláusula OUTPUT (Transact-SQL). O exemplo também mostra como armazenar os OUTPUT resultados da cláusula em uma variável de tabela. E, em seguida, você resume os MERGE resultados da 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. Insira os resultados da instrução MERGE em outra tabela

O exemplo a seguir captura 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 na base de dados AdventureWorks2025, com base nas encomendas processadas na SalesOrderDetail tabela. O exemplo captura as linhas atualizadas e as insere em outra tabela usada para controlar alterações de inventário.

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. Use MERGE para fazer INSERT ou UPDATE em uma tabela de borda de destino em um banco de dados gráfico

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

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

• SELECIONAR (Transact-SQL)
• INSERIR (Transact-SQL)
• ATUALIZAÇÃO (Transact-SQL)
• SUPRIMIR (Transact-SQL)
• Cláusula OUTPUT (Transact-SQL)
• MERGE em pacotes do Integration Services
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)
• Table Value Construtor (Transact-SQL)