Em artigos anteriores conversamos sobre os comandos DML (Data Manipulation Language) de inclusão, alteração e exclusão de dados. Neste artigo conheceremos o comando MERGE, que junta os três comandos anteriores, e pode ser muito útil.
Mas, Dani, se já sabemos os três comandos, por que 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.
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 artigo. 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
De 0 a 10, o quanto você recomendaria este artigo para um amigo?