Banco de Dados

2 out, 2018

Vamos combinar Insert, Delete e Update em um único comando? Conheça o comando MERGE!

100 visualizações
Publicidade

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