MERGE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics

Executa operações de inserção, atualização ou exclusão em uma tabela de destino usando os resultados de uma união com uma tabela de origem. Por exemplo, sincronize duas tabelas inserindo, atualizando ou excluindo linhas em uma tabela com base nas diferenças encontradas na outra tabela.

Observação

Altere o seletor de versão do produto para obter um conteúdo importante na instrução MERGE específico para o Azure Synapse Analytics. Use o Azure Synapse Analytics a fim de alterar a versão do documento para o Azure Synapse Analytics.

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. 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 as instruções básicas INSERT, UPDATE e DELETE. 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);  

Observação

MERGE agora está em disponibilidade geral no Pool de SQL Dedicado do Synapse com a versão '10.0.17829.0' ou superior. Conecte-se ao pool de SQL dedicado (antigo SQL DW) e execute SELECT @@VERSION. Uma pausa e uma retomada podem ser necessárias para garantir que sua instância obtenha a versão mais recente.

Dica

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. 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, considere usar as instruções básicas INSERT, UPDATE e DELETE para melhor desempenho e escalabilidade de consultas. 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);  

Convenções de sintaxe de Transact-SQL

Sintaxe

-- SQL Server and Azure SQL Database
[ 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 . ]  
  target_table  
}  

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

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

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

-- MERGE for Azure Synapse Analytics 
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ INTO ] <target_table> [ [ 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 ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error. 

<target_table> ::=  
{
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}  

<merge_search_condition> ::=  
    <search_condition>  

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

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

<clause_search_condition> ::=  
    <search_condition> 

Argumentos

WITH <common_table_expression>

Especifica a exibição ou o conjunto de resultados nomeado temporário, também conhecido como expressão de tabela comum, que é definida no escopo da instrução MERGE. 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 expressão TOP não são organizadas em nenhuma ordem. Para saber mais, confira TOP (Transact-SQL).

A cláusula TOP aplica-se depois que toda a tabela de origem e toda a tabela de destino são unidas e as linhas unidas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas. A cláusula TOP ainda reduz o número de linhas unidas para o valor especificado. As ações de inserção, atualização ou exclusão aplicam-se às linhas unidas restantes de maneira não ordenada. Ou seja, não há ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN. Por exemplo, a especificação de 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 instrução MERGE poderá executar uma verificação de tabela ou verificação de índice clusterizado na tabela de origem, bem como uma verificação de tabela ou verificação de índice clusterizado na tabela de destino. Portanto, o desempenho de E/S por vezes é afetado mesmo ao usar uma cláusula TOP para modificar uma grande tabela 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 em <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.

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

É possível especificar dicas como um <merge_hint>.

Observe que merge_hints não são compatíveis com o Azure Synapse Analytics.

[ AS ] table_alias

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

USING <table_source>

Especifica a fonte de dados que é compatível com as linhas de dados em target_table com base em <merge_search condition>. O resultado dessa correspondência dita as ações a serem tomadas pelas cláusulas WHEN da instrução MERGE. <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 construir uma tabela especificando várias linhas.

<table_source> pode ser uma tabela derivada que usa SELECT ... UNION ALL para construir uma tabela especificando várias linhas.

[ AS ] 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).

ON <merge_search_condition>

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

Cuidado

É 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 de consulta descartando linhas da tabela de destino na cláusula ON; por exemplo, especificando AND NOT target_table.column_x = value. Isso pode retornar resultados inesperados e incorretos.

WHEN MATCHED THEN <merge_matched>

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

A instrução MERGE pode ter, no máximo, duas cláusulas WHEN MATCHED. Se duas cláusulas forem especificadas, a primeira deverá ser acompanhada de uma cláusula AND <search_condition>. Para qualquer linha especificada, a segunda cláusula WHEN MATCHED será aplicada somente se a primeira não for. Se houver duas cláusulas WHEN MATCHED, uma delas deverá especificar uma ação UPDATE e a outra, uma ação DELETE. Quando UPDATE for especificada na cláusula <merge_matched> e mais de uma linha de <table_source> corresponder a uma linha em target_table com base em <merge_search_condition>, o SQL Server retornará um erro. A instrução MERGE não pode atualizar a mesma linha mais de uma vez, nem atualizar e excluir a mesma linha.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

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

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

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

A instrução MERGE pode ter, no máximo, duas cláusulas WHEN NOT MATCHED BY SOURCE. Se forem especificadas duas cláusulas, a primeira deverá ser acompanhada por uma cláusula AND <clause_search_condition>. Para qualquer linha especificada, a segunda cláusula WHEN NOT MATCHED BY SOURCE será aplicada somente se a primeira não for. 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. Somente as colunas da tabela de destino podem ser referenciadas na <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 a instrução falhar porque não é possível acessar Col1 na tabela de origem.

AND <clause_search_condition>

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

<table_hint_limited>

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

NOLOCK e READUNCOMMITTED não são permitidas. Para obter mais informações sobre dicas de tabela, confiraDicas de tabela (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. 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.

Cuidado

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

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 especificada na cláusula output. $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. Recomendamos usar a cláusula OUTPUT para consultar ou contar linhas afetadas por uma instrução MERGE. Para obter mais informações sobre os argumentos e o comportamento dessa cláusula, confira 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, veja Dicas de consulta (Transact-SQL).

<merge_matched>

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

UPDATE SET <set_clause>

Especifica a lista de nomes de colunas ou de variáveis a serem atualizados na tabela de destino e os valores com os quais eles devem ser atualizados.

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 (excluir)

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 parte única. Caso contrário, haverá falha na instrução MERGE. 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.

DEFAULT VALUES

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, confira Condição de pesquisa (Transact-SQL).

<graph search pattern>

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

Comentários

Observação

No Azure Synapse Analytics, o comando MERGE tem as diferenças a seguir em comparação com o banco de dados SQL do Azure e o SQL Server.

• Não há suporte para usar MERGE para atualizar uma coluna de chave de distribuição em builds mais antigos que 10.0.17829.0. Se não for possível pausar ou forçar a atualização, use a instrução UPDATE FROM ... JOIN ANSI como uma solução alternativa até a versão 10.0.17829.0.
• Uma atualização MERGE é implementada como um par de exclusão e inserção. A contagem de linhas afetadas em uma atualização MERGE inclui as linhas excluídas e inseridas.
• MERGE…WHEN NOT MATCHED INSERT não tem suporte em tabelas com colunas IDENTITY.
• O construtor de valor de tabela não pode ser usado na cláusula USING da tabela de origem. Use SELECT ... UNION ALL para criar uma tabela de origem derivada com várias linhas.
• O suporte para tabelas com tipos de distribuição diferentes é descrito nesta tabela:

Cláusula MERGE no Azure Synapse Analytics	Tabela de distribuição TARGET com suporte	Tabela de distribuição SOURCE com suporte	Comentário
WHEN MATCHED	Todos os tipos de distribuição	Todos os tipos de distribuição
NOT MATCHED BY TARGET	HASH	Todos os tipos de distribuição	Use UPDATE/DELETE FROM…JOIN para sincronizar duas tabelas.
NOT MATCHED BY SOURCE	Todos os tipos de distribuição	Todos os tipos de distribuição

Dica

Se você está usando a chave de hash de distribuição como a coluna JOIN em MERGE e executando apenas uma comparação de igualdade, omita a chave de distribuição da lista de colunas na cláusula WHEN MATCHED THEN UPDATE SET, pois essa é uma atualização redundante.

Importante

No Azure Synapse Analytics, o comando MERGE em builds mais antigos que 10.0.17829.0 pode, em determinadas condições, deixar a tabela de destino em um estado inconsistente, com linhas colocadas na distribuição errada, fazendo com que consultas posteriores retornem resultados incorretos em alguns casos. Esse problema pode ocorrer em dois casos:

Cenário	Comentário
Caso 1 Usando MERGE em uma tabela TARGET distribuída por HASH que contém índices secundários ou uma restrição UNIQUE.	– Corrigido na versão SQL do Synapse 10.0.15563.0 e posterior. – Se SELECT @@VERSION retornar uma versão inferior à 10.0.15563.0, pause e retome manualmente o pool de SQL do Synapse para obter essa correção. – Até que a correção seja aplicada ao seu pool de SQL do Synapse, evite usar o comando MERGE em tabelas TARGET distribuídas em HASH que têm índices secundários ou restrições UNIQUE.
Caso 2 Usando MERGE para atualizar uma coluna de chave de distribuição de uma tabela distribuída HASH.	– Corrigido na versão SQL do Synapse 10.0.17829.0 e posterior. – Se SELECT @@VERSION retornar uma versão inferior à 10.0.17829.0, pause e retome manualmente o pool de SQL do Synapse para obter essa correção. – Até que a correção tenha sido aplicada ao pool de SQL do Synapse, evite usar o comando MERGE para atualizar colunas de chave de distribuição.

Observe que as atualizações em ambos os cenários não reparam tabelas já afetadas pela execução anterior de MERGE. Use os scripts abaixo para identificar e reparar manualmente as tabelas afetadas.

Para verificar quais tabelas distribuídas por hash em um banco de dados podem ser preocupantes (se usadas nos casos acima), execute esta instrução

-- Case 1
select a.name, c.distribution_policy_desc, b.type from sys.tables a join sys.indexes b
on a.object_id = b.object_id
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where b.type = 2 and c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
select a.name, c.distribution_policy_desc from sys.tables a 
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where c.distribution_policy_desc = 'HASH';

Para verificar se uma tabela distribuída de hash de MERGE é afetada pelo Caso 1 ou pelo Caso 2, siga estas etapas para examinar se as tabelas têm linhas entregues na distribuição errada. Se “não for necessário reparar” for retornado, essa tabela não está afetada.

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

create table [check_table_1] with(distribution = round_robin) as
select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> group by <DISTRIBUTION_COLUMN>;
go

create table [check_table_2] with(distribution = hash(x)) as
select x from [check_table_1];
go

if not exists(select top 1 * from (select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> except select x from 
[check_table_2]) as tmp)
select 'no need for repair' as result
else select 'needs repair' as result
go

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

Para reparar as tabelas afetadas, execute estas instruções para copiar todas as linhas da tabela antiga para uma nova.

if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go
if object_id('[repair_table]', 'U') is not null
drop table [repair_table];
go
create table [repair_table_temp] with(distribution = round_robin) as select * from <MERGE_TABLE>;
go

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
create table [repair_table] with(distribution = hash(<DISTRIBUTION_COLUMN>)) as select * from [repair_table_temp];
go
if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go

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

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. Se IGNORE_DUP_KEY for ON em qualquer índice exclusivo na tabela de destino, MERGE vai ignorar essa configuração.

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

Quando usada depois de 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 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.

Cuidado

Não use a instrução MERGE com a replicação de atualização na fila. A instrução MERGE e o gatilho de atualização enfileirada não são compatíveis. Substitua a instrução MERGE por uma instrução de inserção ou de atualização.

Solução de problemas

Em determinados cenários, uma instrução MERGE pode resultar no erro "CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns.", mesmo quando nenhuma tabela de destino nem de origem tem 1024 colunas. Esse cenário pode surgir quando todas as condições abaixo são atendidas:

• Várias colunas são especificadas em uma operação UPDATE SET ou INSERT dentro de MERGE (não específico para qualquer cláusula WHEN [NOT] MATCHED)
• Qualquer coluna na condição JOIN tem um índice não clusterizado (NCI)

Se esse erro for encontrado, a solução alternativa sugerida será remover o NCI (índice não clusterizado) das colunas JOIN ou ingressar em colunas sem um NCI. Se você atualizar mais tarde as tabelas subjacentes para incluir um NCI nas colunas JOIN, a instrução MERGE poderá ser suscetível a esse erro em runtime. Consulte DROP INDEX para saber como remover o índice não clusterizado.

Implementação de gatilho

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

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

Se algum gatilho INSTEAD 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.

Observação

Ao contrário de instruções INSERT, UPDATE e DELETE separadas, o número de linhas refletidas por @@ROWCOUNT dentro de um gatilho poderá ser superior. O @@ROWCOUNT dentro de gatilhos AFTER (não importa as instruções de modificação de dados que o gatilho captura) refletirá o número total de linhas afetadas pela instrução MERGE. Por exemplo, caso uma instrução MERGE insira, atualize, depois exclua uma linha, o @@ROWCOUNT será três para gatilhos AFTER, mesmo se o gatilho for declarado somente para instruções INSERT.

Permissões

Requer a permissão SELECT na tabela de origem e as permissões INSERT, UPDATE ou DELETE na tabela de destino. Para saber mais, confira a seção Permissões nos artigos SELECT, INSERT, UPDATE e DELETE.

Práticas recomendadas de índice

Utilizando a instrução MERGE, 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 instrução MERGE, é recomendável seguir estas diretrizes de índice:

• Criar índices para facilitar a junção entre a origem e o destino da instrução MERGE:
  • 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 usar tabelas com algum tipo de índice columnstore como o destino de instruções MERGE. Tal como ocorre em instruções UPDATE, o desempenho poderá ser mais adequado com índices columnstore atualizando uma tabela rowstore em etapas, depois executando instruções DELETE e INSERT em lote, em vez de uma instrução UPDATE ou MERGE.

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

Em termos de bloqueio, a instrução MERGE é diferente de instruções discretas e consecutivas como INSERT, UPDATE e DELETE. A instrução MERGE ainda executa operações INSERT, UPDATE e DELETE. No entanto, ela usa diferentes mecanismos de bloqueio. Talvez seja mais eficiente escrever instruções discretas como INSERT, UPDATE e DELETE para algumas necessidades do aplicativo. Em escala, a instrução MERGE poderá apresentar problemas complicados de simultaneidade ou exigir uma solução de problemas avançada. Portanto, planeje testar instruções MERGE de modo abrangente antes de implantá-las na produção.

As instruções MERGE são uma substituição adequada para executar operações discretas como INSERT, UPDATE e DELETE (porém não se limitam a elas) 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 há expectativa de uma simultaneidade pesada, a lógica de instruções INSERT, UPDATE e DELETE separadas poderá funcionar de modo mais adequado, com menos bloqueio do que uma instrução MERGE.
• É 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 inserir e atualizar chaves exclusivas por meio da instrução MERGE, especificar uma dica HOLDLOCK evitará violações de chaves exclusivas. HOLDLOCK é um sinônimo de nível de isolamento da transação SERIALIZABLE, que não permite que outras transações simultâneas modifiquem os dados que essa transação leu. SERIALIZABLE é o nível de isolamento mais seguro, porém fornece o mínimo de simultaneidade com outras transações que retêm bloqueios em intervalos de dados para evitar a inserção ou atualização de linhas fantasmas durante a execução de leituras. Para obter mais informações sobre a dica HOLDLOCK, confira Dicas e SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Práticas recomendadas de JOIN

Para melhorar o desempenho da instrução MERGE e assegurar a obtenção dos resultados corretos, é recomendável seguir estas diretrizes de junção:

• Especifique apenas condições de pesquisa na cláusula ON <merge_search_condition> que determinem os critérios para corresponder dados nas tabelas de origem e de destino. 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 o critério de pesquisa para filtragem de linhas na cláusula WHEN apropriada. 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 a especificar critérios adicionais de pesquisa na cláusula ON 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 na instrução SELECT. 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 determinada junção especificando a cláusula OPTION (<query_hint>) na instrução MERGE. É recomendável não usar a junção hash como dica de consulta para instruções MERGE, pois esse tipo de junção não usa índices.

Práticas recomendadas de parametrização

Se uma instrução SELECT, INSERT, UPDATE ou DELETE for executada sem parâmetros, o otimizador de consulta do SQL Server poderá parametrizar a instrução internamente. 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 de modo interno como INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Esse processo, chamado de parametrização simples, aumenta a capacidade do mecanismo relacional de comparar as novas instruções SQL com planos de execução existentes anteriormente compilados. 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 instruções MERGE. Por isso, as instruções MERGE que contêm valores literais podem não executar as instruções INSERT, UPDATE ou DELETE individuais, pois um novo plano é compilado sempre que a instrução MERGE é executada.

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

• Parametrize todos os valores literais na cláusula ON <merge_search_condition> e nas cláusulas WHEN da instrução MERGE. Por exemplo, você pode inserir a instrução MERGE a um procedimento armazenado que substitua 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 instruções MERGE 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.

Práticas recomendadas da cláusula TOP

Na instrução MERGE, a cláusula TOP especifica o número ou o percentual de linhas que são afetadas depois que as tabelas de origem e de destino são unidas e que as linhas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas. 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. Ou seja, não há ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN. 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.

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

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

  A instrução MERGE faz uma verificação completa das tabelas de origem e de destino. Dividir a operação em lotes diminui o número de operações de gravação executadas por lote; no entanto, cada lote fará uma verificação completa das tabelas de origem e de destino. 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 cláusula ON para determinar quais linhas de origem afetam linhas de destino existentes e quais são genuinamente novas.
  • Use uma condição adicional na cláusula WHEN MATCHED para determinar se a linha de destino já foi atualizada por um lote anterior.
  • Use uma condição adicional na cláusula WHEN MATCHED e a lógica SET para verificar se não é possível atualizar a mesma linha duas vezes.

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

Práticas recomendadas de carregamento em massa

A instrução MERGE pode ser usada para carregar com eficiência dados de carregamento em massa de um arquivo de dados de origem em uma tabela de destino especificando-se a cláusula OPENROWSET(BULK…) como tabela de origem. 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 instrução MERGE de carregamento em massa e habilite-os posteriormente. Isso é comum e útil em operações noturnas de carregamento em massa.

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

  Por padrão, a operação em massa presume que o arquivo de dados não está ordenado. Por isso, é importante que os dados de origem sejam classificados de acordo com o índice clusterizado na tabela de destino e que a dica ORDER seja usada para indicar a ordem, de modo que o otimizador de consulta possa gerar um plano de consulta mais eficaz. 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.

Avaliação e diagnóstico do desempenho de MERGE

Os recursos a seguir estão disponíveis para ajudar você a avaliar e diagnosticar o desempenho de instruções MERGE.

• Use o contador merge stmt na exibição de gerenciamento dinâmico sys.dm_exec_query_optimizer_info para retornar o número de otimizações de consulta relativas a instruções MERGE.
• Use o atributo merge_action_type na exibição de gerenciamento dinâmico sys.dm_exec_plan_attributes para retornar o tipo de plano de execução de gatilho usado como resultado de uma instrução MERGE.
• Use uma Sessão de Eventos Estendidos para coletar dados de solução de problemas para a instrução MERGE tal como ocorre em outras instruções DML (linguagem de manipulação de dados). Para obter mais informações sobre Eventos Estendidos, confira Início rápido: eventos estendidos no SQL Server e no SSMS XEvent Profiler.

Exemplos

a. Usar 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 uma linha correspondente não existir. Normalmente, seja qual for o cenário, você transmite parâmetros para um procedimento armazenado que contém as instruções UPDATE e INSERT apropriadas. Com a instrução MERGE, você pode executar as duas tarefas em uma única instrução. O exemplo a seguir mostrará um procedimento armazenado no banco de dados AdventureWorks2019 que contém as instruções INSERT e UPDATE. O procedimento é então modificado para executar as operações equivalentes usando uma única instrução MERGE.

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  

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

-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');  
GO  

B. Usar MERGE para executar as operações UPDATE e DELETE em uma tabela em uma única instrução

O exemplo a seguir usa MERGE para atualizar diariamente a tabela ProductInventory no banco de dados de exemplo AdventureWorks2019 com base em pedidos processados na tabela SalesOrderDetail. 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  
    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';  

CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS tgt  
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 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();  
GO  

EXECUTE Production.usp_UpdateInventory '20030501';  

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

O exemplo a seguir usa MERGE para modificar a tabela SalesReason no banco de dados AdventureWorks2019 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).

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

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

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 é derivada e usa SELECT ... UNION ALL para especificar várias linhas da tabela de origem.

MERGE INTO Sales.SalesReason AS tgt  
USING (SELECT 'Recommendation','Other' UNION ALL 
       SELECT 'Review', 'Marketing' UNION ALL 
       SELECT '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);  

D. Inserindo os resultados da instrução MERGE em outra tabela

O exemplo a seguir captura dados retornados da cláusula OUTPUT de uma instrução MERGE e insere esses dados em outra tabela. A instrução MERGE atualiza diariamente a coluna Quantity da tabela ProductInventory no banco de dados AdventureWorks2019 com base em pedidos processados na tabela SalesOrderDetail. 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  
            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. Você usa a instrução MERGE na borda livesIn para inserir uma nova linha se a borda ainda não existir entre um Person e City. 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

Confira também

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• cláusula OUTPUT (Transact-SQL)
• MERGE em pacotes do Integration Services
• FROM (Transact-SQL)
• Construtor de valor de tabela (Transact-SQL)

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• MERGE em pacotes do Integration Services
• FROM (Transact-SQL)
• Construtor de valor de tabela (Transact-SQL)