SQL Server – Como utilizar o comando MERGE para inserir, atualizar e apagar dados com apenas 1 comando | iMasters
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:
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
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:
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:
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!
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.
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:
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:
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:
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".
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