Em posts anteriores nós conversamos sobre os comandos DML (Data Manipulation Language) de inclusão, alteração e exclusão de dados. Neste post conheceremos o comando MERGE que junta os 3 comandos anteriores, e pode ser muito útil.
Mas Dani, se já sabemos os 3 comandos porque mais um? Porque nosso tempo é muito valioso e devemos conhecer formas de atingirmos nossos objetivos com qualidade, e em pouco tempo.
Quando usar o comando MERGE?
Use o comando MERGE nas seguintes situações:
- quando você precisar inserir ou atualizar as linhas de uma tabela condicionalmente. Em outras palavras, se uma linha existir na tabela de destino, atualize os valores de uma ou várias colunas. Se não existir, insira uma nova linha.
- Quando você precisar sincronizar os dados de duas tabelas. Ou seja, insira, atualize ou exclua os dados da tabela de destino com base nas diferenças resultantes da comparação com a tabela de origem.
Sintaxe básica do comando MERGE
- Cláusula MERGE
- Onde informamos a tabela de destino das operações (onde as operações de inclusão, exclusão e alteração serão realizadas)
- Cláusula USING
- Onde informamos a tabela de origem (a que será usada nas comparações e NÃO será alterada)
- Cláusula ON
- Contém as condições que juntam as informações das tabelas (sim o JOIN!!!)
- Cláusula WHEN
- Nesta cláusula especificamos a operação (INSERT, DELETE, UPDATE) que queremos realizar, e informamos também quando queremos realizar:
- WHEN MATCHED
- Quando a condição de junção for obedecida, ou seja existe um registro na tabela de origem e de destino de acordo com a condição da cláusula ON
- WHEN NOT MATCHED BY TARGET
- Quando o registro não existe na tabela de destino
- WHEN NOT MATCHED BY SOURCE
- Quando o registro não existe na tabela de origem
- Cláusula OUTPUT
- Retorna uma linha para cada linha incluída, excluída e alterada.
- Nesta cláusula podemos usar:
- A variável interna $action, que informa a operação que foi realizada;
- As tabelas temporárias Inserted e Deleted, que são criadas e mantidas pelo SQL Server, e que contém as informações incluídas e excluídas respectivamente.
- WHEN MATCHED
- Nesta cláusula especificamos a operação (INSERT, DELETE, UPDATE) que queremos realizar, e informamos também quando queremos realizar:
Nosso cenário
Em nosso banco de dados temos a tabela FactBuyingHabits que contém a última data que um cliente comprou um produto específico. Temos também uma segunda tabela chamada Purchases que é uma tabela transacional e contém os dados das compras dos clientes.
A tabela FactBuyingHabits é carregada semanalmente com as seguintes condições:
– Se o produto não existe na tabela FactBuyingHabits deve ser incluído
– Se o produto já existe a data da última compra deve ser atualizada.
Lembrando que este é um cenário hipotético e simplificado, com o único objetivo de te mostrar como o comando funciona. No seu dia a dia as condições podem ser outras, e podem ser bem mais complexas, e mesmo assim acho que você vai “tirar de letra”!
Nosso script
Lembrando que o script está disponível no GitHub e que a VM que estamos usando está disponível para download.
Dica da Dani… Sempre teste o comando antes de executá-lo!!! Faça isso usando uma transação. Veja no exemplo que abri uma transação, e fiz um rollback no final. Ou seja, nada foi realizado no banco de dados e eu pude ver se meu comando estava correto. Só depois de ter certeza, troque o rollback pelo commit, e aí sim você efetivará as alterações.
--Se a tabela existe, dropar IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL DROP TABLE dbo.Purchases; GO --Criar a tabela Purchases CREATE TABLE dbo.Purchases ( ProductID int, CustomerID int, PurchaseDate datetime, CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID)); GO --Inserir dados na tabela Purchases INSERT INTO dbo.Purchases VALUES(707, 11794, '20180321'), (707, 15160, '20180325'),(708, 18529, '20180321'), (711, 11794, '20180321'),(711, 19585, '20180322'), (712, 14680, '20180325'),(712, 21524, '20180325'), (712, 19072, '20180321'),(870, 15160, '20180323'), (870, 11927, '20180324'),(870, 18749, '20180325'); GO --Se a tabela FactBuyingHabits existe, dropar IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL DROP TABLE dbo.FactBuyingHabits; GO --Criar a tabela FactBuyingHabits CREATE TABLE dbo.FactBuyingHabits ( ProductID int, CustomerID int, LastPurchaseDate datetime, CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID)); GO --Insserir dados na tabela FactBuyingHabits INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20180314'), (707, 18178, '20180318'),(864, 14114, '20180318'), (866, 13350, '20180318'),(866, 20201, '20180315'), (867, 20201, '20180314'),(869, 19893, '20180315'), (870, 17151, '20180318'),(870, 15160, '20180317'), (871, 21717, '20180317'),(871, 21163, '20180315'), (871, 13350, '20180315'),(873, 23381, '20180315'); GO BEGIN TRANSACTION MERGE dbo.FactBuyingHabits AS Destino USING dbo.Purchases AS Origem --Condição: O produto existe nas 2 tabelas e o cliente também ON (Destino.ProductID = Origem.ProductID AND Destino.CustomerID = Origem.CustomerID) --Se a condição for obedecida, ou seja, existem registros nas duas tabelas WHEN MATCHED THEN UPDATE SET Destino.LastPurchaseDate = Origem.PurchaseDate --Se a condição não foi obedecida porque o registro não existe na tabela de destino WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, ProductID, LastPurchaseDate) VALUES (Origem.CustomerID, Origem.ProductID, Origem.PurchaseDate) OUTPUT $action, Inserted.*, Deleted.*; ROLLBACK TRANSACTION --nos testes use o rolloback até ter certeza que o comando está ok. Depois do teste comente esta linha! --COMMIT TRANSACTION -- só depois de testar, remova o comentário desta linha.
Conclusão
Este é um comando mais avançado, e se você o compreendeu mostra que você está se tornando um programador melhor a cada post! Parabéns!
Creio que você percebeu que para usar este comando com sucesso, você precisa ir além de escrever um comando, precisa efetivamente entender a necessidade de negócio que o comando vai atender.
Referências e Links úteis