Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como utilizar o comando MERGE para inserir, atualizar e apagar dados com apenas 1 comando

Post Views 25,537 views
Reading time 11 minutes

Hey Guys!

Neste post, gostaria de demonstrar a vocês como utilizar o comando MERGE, disponível desde o SQL Server 2008, para realizar comandos de INSERT e UPDATE entre duas tabelas em uma única instrução.

Para os exemplos desse post, vou utilizar uma base que vou criar utilizando o script abaixo:

E as nossas tabelas de origem e destino ficam assim:

Introduction

Atividade muito comum no dia a dia de profissionais que trabalham com dados, especialmente quem trabalha com ETL, a tarefa de atualizar uma tabela baseado nos dados de uma outra tabela, costuma acabar gerando 2 operações distintas para a atualização dos dados:

Método 1: Insert/Update

  • Atividade 1: Atualizar os registros que existem nas duas tabelas
  • Atividade 2: Inserir os registros que só existem na tabela de origem

Vamos ver como faríamos isso:

E como ficaram os dados da tabela?

Método 2: Delete/Insert

  • Atividade 1: Apaga os registros que existem nas duas tabelas
  • Atividade 2: Inserir os registros que da tabela de origem

A qual podemos entender como ela funciona através do script abaixo:

Result:

Utilizando o comando MERGE para INSERT + UPDATE + DELETE

Até agora, não apresentei nenhuma novidade, né? Comandos de DELETE/INSERT e UPDATE/INSERT são bem comuns no dia a dia de profissionais da área de dados.

Entretanto, para resolver esse problema, a partir do SQL Server 2008 podemos utilizar o comando MERGE, que nos permite realizar essa mesma atividade em apenas 1 instrução. Seu funcionamento é simples: Uma ou mais colunas das tabelas envolvidas são consideradas chaves (identificadores) para caso o valor da chave exista na tabela destino, os valores serão atualizados de acordo com a tabela origem. Caso esse identificador não exista, esse registro será inserido na tabela destino.

Sua sintaxe funciona assim:

Result:

Controlando a cláusula NOT MATCHED na Origem e Destino

Podemos também, controlar quando um registro não existe no destino ou na origem, e tomar decisões diferentes para essas 2 situações:

Atualizando valores específicos, através de filtros específicos

Pode ser necessário atualizar apenas a quantidade e valor, e nos casos em que estes forem diferentes:

Exibindo uma saída com todas as alterações realizadas pelo MERGE

E se quisermos coletar os resultados do MERGE e exibir as operações realizadas na tela ? Basta usar o OUTPUT!

Result:

Armazenando a saída do MERGE em uma tabela

Gostaria de salvar os registros de log em uma tabela para consultar os dados ?

Utilizando consultas como dados de origem

O MERGE também nos permite juntar dados vindos de consultas ao invés de tabelas fixas, e utilizar JOINS, caso necessário.

Resultado – vejam que, como trouxe apenas os dados do dia 22/09 no MERGE, os dados do dia 21/09 que foram carregados manualmente antes do MERGE foram apagados da tabela de destino, pois não existiam na origem

Utilizando CTE com JOIN para gerar dados da origem

O Merge também permite utilizar CTE para geração do resultset de origem dos dados e utilizar JOINS, caso necessário.

Result:

Cuidados ao utilizar o MERGE

Agora que demonstrei várias formas de utilizar o MERGE no SQL Server, gostaria de alertá-los sobre a existência de alguns bugs ao utilizar o MERGE no SQL Server 2008, que vão desde a saídas incorretas ao utilizar o OUTPUT a erros severos como o demonstrado abaixo:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Se você está utilizando a versão 2008 (AINDA), atualize o seu SQL com a última versão do SP e Cumulative Update disponível e leia com atenção aos artigos que vou disponibilizar abaixo, pois eles tem vários cenários que podem gerar dor de cabeça ao utilizar o MERGE no SQL Server 2008 (nas versões 2012+ esses bugs não existem mais):

Utilizando a Stored Procedure stpETL_Upsert

Para facilitar o uso de MERGE, especialmente em tabelas com muitas colunas, vou compartilhar com vocês a Stored Procedure stpETL_Upsert, criada originalmente pelo Murilo Mielke e adaptada por mim, que tem como objetivo, facilitar o uso de MERGE em situações do cotidiano.

Parâmetros da SP:

  • @Nm_Source: Parâmetro do tipo VARCHAR(MAX), ele contém o nome da tabela de origem dos dados (source). O nome da tabela deve ser especifico na forma completa (database.schema.tabela)
  • @Nm_Target: Parâmetro do tipo VARCHAR(MAX), ele contém o nome da tabela de destino dos dados (target), ou seja, onde os dados serão inseridos/atualizados. O nome da tabela deve ser especifico na forma completa (database.schema.tabela)
  • @Cd_Join: Parâmetro do tipo VARCHAR(MAX), deve ser informado a coluna ou as colunas do JOIN para ligar os dados da tabela origem (source) e destino (target). Para o uso simplificado, você pode especificar apenas os nomes das colunas, caso seja o mesmo entre a origem e o destino, separando por vírgula “,” caso haja mais de uma coluna para realizar o JOIN. Caso o nome das colunas entre origem e destino sejam diferentes, deve-se utilizar o formato nome_coluna_origem:nome_coluna_destino, utilizando também o separador vírgula “,” em caso de múltiplas colunas
  • @Cd_Chave: Parâmetro do tipo VARCHAR(MAX), deve ser informado o nome da coluna sequencial (IDENTITY) da tabela destino, para evitar que o SP tente realizar um UPDATE ou INSERT utilizando essa coluna. Caso não tenha coluna IDENTITY no destino (target), pode-se ignorar esse parâmetro, uma vez que seu valor padrão já é vazio.
  • @Fl_Update: Parâmetro do tipo BIT, com valor padrão = 1, essa coluna informa à SP se os registros na tabela de destino (target) devem ser atualizados caso existam na origem e no destino (@Fl_Update = 1) ou não.
  • @Fl_Debug: Parâmetro do tipo BIT que faz com que a SP mostre na janela de saída o comando MERGE gerado ao final da execução do mesmo

Exemplo 1 – Uso simples

Utilizando o mesmo cenário dos exemplos desse artigo, vamos utilizar a stpETL_Upset para realizar nosso MERGE:

Result:

Código do MERGE gerado:

Exemplo 2 – Utilizando várias colunas para o JOIN

Caso você precise utilizar várias colunas para realizar o JOIN dos dados, basta separá-los por vírgula “,” na chamada da SP:

Código gerado:

Exemplo 3 – Utilizando JOINS com colunas diferentes

Caso as colunas do JOIN não tenham exatamente o mesmo nome ou você queira utilizar mais de uma cláusula do JOIN, você pode utilizar a SP conforme demonstrado abaixo, utilizando a sintaxe nome_coluna_tabela_origem:nome_coluna_tabela_destino, podendo ainda, utilizar o caractere vírgula “,” para utilizar vários mapeamentos.

No exemplo abaixo, a tabela de origem “Venda” possui um campo chamado Id_Venda, que vai realizar o JOIN com a coluna Cod_Venda na tabela de destino “Dim_Venda”.

Código gerado:

Código-fonte

Gostou da stpETL_Upset? Bem prática né? Vou disponibilizar o código-fonte dessa Stored Procedure aqui embaixo para que vocês possam começar a utilizá-la no seu dia a dia. Não se esqueçam de criar os objetos fncSplit e fncSplitTexto que estão nos requisitos hein, senão vai dar erro e vocês vão reclamar comigo nos comentários.. kkkkk
Visualizar código-fonte da stpETL_Upsert

Requisitos:

References

Bom, pessoal!
Espero que tenham gostado desse post e até a próxima.
Grande abraço!