Introdução à instrução MERGE e modificação de dados do SQL Server

A instrução MERGE é usada para fazer alterações em uma tabela com base em valores correspondentes a outra. Ele pode ser usado para combinar operações de inserção, atualização e exclusão em uma instrução. Neste artigo, exploraremos como usar a instrução MERGE. Discutimos algumas práticas recomendadas, limitações e resumos com vários exemplos.

Este é o quinto artigo de uma série de artigos. Você pode começar do início lendo Introdução às instruções de modificação de dados do SQL Server.

Todos os exemplos desta lição são baseados no Microsoft SQL Server Management Studio e no banco de dados AdventureWorks2012. Você pode começar a usar essas ferramentas gratuitas em meu Guia de primeiros passos com o SQL Server

Antes de começar

Embora este artigo use o banco de dados AdventureWorks para seus exemplos, decidi criar várias tabelas de exemplo para uso no banco de dados para ajudar a ilustrar melhor os conceitos cobertos. Você pode encontrar o script de que precisa para executar aqui. Observe que há uma seção especial relacionada a MERGE.

Estrutura básica

A instrução MERGE combina as operações INSERT, DELETE e UPDATE em uma tabela. Depois de entender como funciona, você verá que simplifica o procedimento com o uso de todas as três instruções separadamente para sincronizar os dados.

Abaixo está um formato generalizado para a instrução merge.

MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement

A instrução merge funciona usando duas tabelas, sourceTable e targetTable. A targetTable é a tabela a ser modificada com base nos dados contidos na sourceTable.

As duas tabelas são comparadas usando um mergeCondition . Esta condição especifica como as linhas de sourceTable são combinadas com targetTable. Se estiver familiarizado com INNER JOINS, você pode pensar nisso como a condição de junção usada para combinar linhas.

Normalmente, você corresponderia a um identificador exclusivo, como uma chave primária. Se a tabela de origem fosse NewProduct e ProductMaster de destino e a chave primária para ambos ProductID, uma boa condição de mesclagem a ser usada seria:

NewProduct.ProductID = ProductMaster.ProductID

Uma condição de mesclagem resulta em um dos três estados: MATCHED, NOT MATCHED ou NOT MATCHED BY SOURCE.

Condições de mesclagem

Vamos repassar o que as várias condições significam:

MATCHED – são linhas que satisfazem a condição de correspondência. Eles são comuns às tabelas de origem e de destino. Em nosso diagrama, eles são mostrados em verde. Quando você usa essa condição em uma declaração de fusão, você; mais como atualizar as colunas da linha de destino com os valores da coluna sourceTable.

NOT MATCHED – Isso também é conhecido como NOT MATCHED BY TARGET; são linhas da tabela de origem que não correspondem a nenhuma linha da tabela de destino. Essas linhas são representadas pela área azul acima. Na maioria dos casos, isso pode ser usado para inferir que as Linhas de origem devem ser adicionadas à tabela de destino.

NÃO CORRESPONDIDAS À FONTE – são linhas na tabela de destino que nunca foram correspondidas por um registro de origem; essas são as linhas na área laranja. Se o seu objetivo é sincronizar completamente os dados targetTable com a fonte, então você usará esta condição de correspondência para DELETE linhas.

Se você está tendo problemas para entender como isso funciona, considere que a condição de mesclagem é como uma condição de junção. As linhas na seção verde representam as linhas que correspondem à condição de mesclagem, as linhas na seção azul são as linhas encontradas na SourceTable, mas não no destino. As linhas na seção laranja são aquelas encontradas apenas no destino.

Com esses cenários correspondentes, você pode facilmente incorporar atividades de adição, remoção e atualização em uma única instrução para sincronizar as alterações entre dois tabelas.

Vejamos um exemplo.

Exemplo de MERGE

Vamos supor que nosso objetivo é sincronizar todas as alterações feitas em esqlProductSource com esqlProductTarget. Aqui está um diagrama dessas duas tabelas:

Nota: Por causa deste exemplo, executei os scripts de que falei na introdução para criar e preencher duas tabelas: esqlProductSource e esqlProductTarget.

Antes de construirmos a instrução MERGE, vamos ver como sincronizaríamos a tabela usando as instruções UPDATE, INSERT e DELETE para modificar, adicionar e remova linhas na tabela de destino.

Acho que depois de ver como fazemos isso individualmente, ver combinados em uma única operação faz mais sentido.

Usando UPDATE para sincronizar alterações de uma tabela para a próxima

Para atualizar a tabela de destino com os valores alterados na origem do produto, podemos usar uma instrução UPDATE. Dado que o ProductID é a chave primária de ambas as tabelas, ele se tornou nossa melhor escolha para combinar linhas entre as tabelas.

Se íamos atualizar os valores da coluna na tabela de destino usando a coluna de origem, poderíamos fazer isso usando a seguinte instrução de atualização

UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID

Esta instrução irá atualizar a coluna em esqlProductTarget com os valores de coluna correspondentes encontrados em esqlProductSource para a correspondência de productIDs.

INSERT linhas encontradas em uma tabela, mas não na outra

Agora vamos ver como nós pode identificar as linhas da tabela de origem que precisamos inserir no destino do produto. Para fazer isso, podemos usar a subconsulta para encontrar linhas na tabela de origem que não estão no destino.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Observação: eu também poderia usar uma junção externa para fazer o mesmo. Se você estiver interessado em saber o motivo, verifique este artigo.

Esta instrução irá inserir uma nova linha em esqlProductTarget de todas as linhas em esqlProductSource que não são encontradas em esqlProductTarget.

Removendo Linhas

A última atividade de sincronização que precisamos fazer, ela remove todas as linhas da tabela de destino que não estão no código-fonte SQL. Como fizemos com a instrução insert, usaremos uma subconsulta. Mas desta vez, iremos identificar as linhas em esqlProductTarget não encontradas em esqlProductSource. Aqui está a instrução DELETE que podemos usar:

DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)

Agora que você viu como fazer as várias operações individualmente, vamos ver como elas se juntam no instrução merge.

Observe que a maior parte do trabalho pesado é feito pela condição de mesclagem e seus resultados. Em vez de configurar repetidamente a correspondência, como fizemos na instrução delete, isso é feito uma vez.

Compare novamente a instrução Insert com a instrução merge acima.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Dado que a instrução MERGE estabelece a tabela de origem e de destino, bem como a forma como eles combinam, tudo codificado em vermelho é redundante; portanto, não na parte de inserção da mesclagem.

Registrando alterações de MERGE usando OUTPUT

Você pode usar a cláusula OUTPUT para registrar quaisquer alterações. Neste caso, a variável especial $ action pode ser usada para registrar a ação de fusão. Esta variável terá um de três valores: “INSERT”, “UPDATE” ou “DELETE”.

Continuaremos a usar nosso exemplo, mas desta vez registraremos as alterações e resumiremos o alterações.

Se o acima for executado em dados de amostra novos, o seguinte resumo é gerado: