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

Visualizações: 24.406 views
Tempo de Leitura: 11 minutos

OlĂ¡ pessoal!

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:

IntroduĂ§Ă£o

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:

Resultado:

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:

Resultado:

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!

Resultado:

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.

Resultado:

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:

Resultado:

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:

ReferĂªncias

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