Inserindo, atualizando e excluindo dados usando MERGE
No SQL Server 2008, é possível executar operações de inserção, atualização ou exclusão em uma única instrução usando a instrução MERGE. A instrução MERGE permite unir uma fonte de dados com uma exibição ou tabela de destino e, em seguida, executar várias ações no destino com base nos resultados dessa união. Por exemplo, você pode usar a instrução MERGE para executar as seguintes operações:
Inserir ou atualizar linhas em uma única tabela de destino condicionalmente.
Se a linha existir na tabela de destino, atualize uma ou mais colunas; caso contrário, insira os dados em uma nova linha.
Sincronizar duas tabelas.
Insira, atualize ou exclua linhas de uma tabela de destino com base nas diferenças em relação aos dados de origem.
A sintaxe de MERGE consiste em cinco cláusulas primárias:
A cláusula MERGE especifica a tabela ou exibição que é o destino das operações de inserção, atualização ou exclusão.
A cláusula USING especifica a fonte de dados adicionada ao destino.
A cláusula ON especifica as condições da junção que determina onde a origem e o destino coincidem.
As cláusulas WHEN (WHEN MATCHED, WHEN NOT MATCHED BY TARGET e WHEN NOT MATCHED BY SOURCE) especificam as ações a serem executadas com base nos resultados da cláusula ON e em quaisquer critérios de pesquisa adicionais especificados nas cláusulas WHEN.
A cláusula OUTPUT retorna uma linha para cada linha no destino que é inserida, atualizada ou excluída.
Para obter detalhes completos sobre a sintaxe e regras, consulte MERGE (Transact-SQL).
Especificando condições de pesquisa de origem e de destino
É importante compreender como os dados de origem e de destino são mesclados em um único fluxo de entrada, e como critérios adicionais de pesquisa podem ser usados para filtrar corretamente linhas não necessárias. Caso contrário, você poderá especificar critérios adicionais de pesquisa de uma maneira que produza resultados incorretos.
As linhas da origem são correspondidas com as linhas do destino com base no predicado de junção especificado na cláusula ON. O resultado é um fluxo de entrada combinado. Uma operação de inserção, atualização ou exclusão é executada por linha de entrada. Dependendo da cláusula WHEN especificada na instrução, a linha de entrada poderá ser qualquer uma das seguintes:
Um par correspondente consistindo em uma linha do destino e em uma linha da origem. Esse é o resultado da cláusula WHEN MATCHED.
Uma linha da origem que não tem nenhuma linha correspondente no destino. Esse é o resultado da cláusula WHEN NOT MATCHED BY TARGET.
Uma linha do destino que não tem nenhuma linha correspondente na origem. Esse é o resultado da cláusula WHEN NOT MATCHED BY SOURCE.
A combinação das cláusulas WHEN especificadas na instrução MERGE determina o tipo de junção implementada pelo processador da consulta e afeta o fluxo de entrada resultante. Para ilustrar, considere as tabelas e os dados de origem e destino de exemplo a seguir.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
A tabela a seguir lista os possíveis tipos de junção e indica quando cada tipo é implementando pelo otimizador de consulta. A tabela também mostra o fluxo de entrada resultante para as tabelas de origem e de destino de exemplo quando o critério da pesquisa para corresponder os dados de origem e de destino for Source.EmployeeID = Target.EmployeeID.
Tipo de junção |
Implementação |
Resultados do fluxo de entrada de exemplo |
|---|---|---|
INNER JOIN |
A cláusula WHEN MATCHED é a única cláusula WHEN especificada. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL |
LEFT OUTER JOIN |
A cláusula WHEN NOT MATCHED BY TARGET é especificada, mas a cláusula WHEN NOT MATCHED BY SOURCE não é especificada. A WHEN MATCHED pode ou não ser especificada. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL |
RIGHT OUTER JOIN |
As cláusulas WHEN MATCHED e WHEN NOT MATCHED BY SOURCE são especificadas, mas a cláusula WHEN NOT MATCHED BY TARGET não é especificada. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve |
FULL OUTER JOIN |
As cláusulas WHEN NOT MATCHED BY TARGET e WHEN NOT MATCHED BY SOURCE são especificadas. A WHEN MATCHED pode ou não ser especificada. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve |
ANTI SEMI JOIN |
A cláusula WHEN NOT MATCHED BY SOURCE é a única cláusula WHEN especificada. |
TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano |
Os resultados do fluxo de entrada de exemplo mostram que os resultados dependem da combinação das cláusulas WHEN. Agora suponha que você queira executar as seguintes ações na tabela de destino com base naquele fluxo de entrada:
Inserir linhas da tabela de origem quando a ID do funcionário não existir na tabela de destino e o nome do funcionário de origem começar com 'S'.
Excluir linhas da tabela de destino quando o nome do funcionário de destino começar com 'S' e a ID do funcionário não existir na tabela de origem.
Para executar essas ações, as seguintes cláusulas WHEN são necessárias:
WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE
Conforme descrito na tabela anterior, quando as duas cláusulas WHEN NOT MATCHED são especificadas, o fluxo de entrada resultante é uma junção externa completa das tabelas de origem e de destino. Agora que os resultados do fluxo de entrada são conhecidos, considere como as ações de inserção, atualização e exclusão serão aplicadas ao fluxo de entrada.
Conforme mencionado anteriormente, as cláusulas WHEN especificam as ações a serem tomadas com base nos resultados da cláusula ON e em quaisquer critérios adicionais de pesquisa especificados nas cláusulas WHEN. Em muitos casos, as condições de pesquisa especificadas na cláusula ON produzem o mesmo fluxo de entrada necessário. No entanto, no cenário de exemplo, as ações de inserção e exclusão precisam de filtragem adicional para restringir as linhas afetadas às que têm um nome de funcionário que começa com 'S'. No exemplo a seguir, as condições de filtragem são aplicadas a WHEN NOT MATCHED BY TARGET e a WHEN NOT MATCHED BY SOURCE. A saída da instrução mostra que as linhas esperadas do fluxo de entrada estão corrigidas, inseridas ou excluídas.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
A seguir são apresentados os resultados da cláusula OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 linhas afetadas)
A redução do número de linhas do fluxo de entrada no início do processo especificando a condição adicional de pesquisa à cláusula ON (por exemplo, especificando ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') talvez pareça que melhore o desempenho da consulta. No entanto, esse procedimento pode provocar resultados inesperados e incorretos. Como as condições adicionais da pesquisa especificadas na cláusula ON não são usadas para corresponder os dados de origem e de destino, elas podem ser mal aplicadas.
O exemplo a seguir demonstra como podem ocorrer resultados incorretos. A condição de pesquisa para corresponder as tabelas de origem e de destino e a condição adicional de pesquisa para filtrar linhas são ambas especificadas na cláusula ON. Como a condição adicional da pesquisa não é necessária para determinar a correspondência da origem e do destino, as ações de inserção e de exclusão são aplicadas a todas as linhas de entrada. De fato, a condição de filtragem EmployeeName LIKE 'S%' é ignorada. Quando a instrução é executada, a saída das tabelas inserted e deleted mostra que duas linhas foram modificadas incorretamente: Mary foi excluída incorretamente da tabela de destino e BOB foi inserido incorretamente.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
A seguir são apresentados os resultados da cláusula OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 linhas afetadas)
Diretrizes para condições de pesquisa
As condições de pesquisa usadas para corresponder as linhas de origem e de destino e as condições adicionais de pesquisa usadas para filtrar linhas da origem ou do destino devem ser especificadas corretamente para garantir que os resultados corretos sejam obtidos. É recomendável seguir estas diretrizes:
Especifique apenas condições de pesquisa na cláusula ON <merge_search_condition> que determinem os critérios para corresponder dados nas tabelas de origem e de destino. Isto é, especifique apenas as colunas da tabela de destino que são comparadas com as colunas correspondentes da tabela de origem.
Não inclua comparações com outros valores, como uma constante.
Para filtrar linhas das tabelas de origem e de destino, use um dos seguintes métodos:
Especifique a condição de pesquisa para filtragem de linhas na cláusula WHEN apropriada. Por exemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Defina uma exibição na origem ou no destino que retorne as linhas filtradas e faça referência à exibição como a tabela de origem ou de destino. Se a exibição for definida na tabela de destino, quaisquer ações sobre ela deverão atender às condições para atualização das exibições. Para obter mais informações sobre como atualizar dados usando uma exibição, consulte Modificando dados por meio de uma exibição.
Use a cláusula de <expressão de tabela comum> WITH para filtrar linhas das tabelas de origem e de destino. Esse método é semelhante a especificar critérios adicionais de pesquisa na cláusula ON e pode produzir resultados incorretos. É recomendável evitar o uso desse método ou testá-lo criteriosamente antes de implementá-lo.
Exemplos
A. Usando uma única instrução MERGE para executar operações INSERT e UPDATE
Vamos supor que você tem a tabela FactBuyingHabits em um banco de dados de data warehouse que rastreia a última data em que cada cliente comprou um produto específico. Uma segunda tabela, Purchases, em um banco de dados OLTP, registra as compras durante uma dada semana. A cada semana, você deseja adicionar linhas de produtos que clientes específicos nunca compraram antes da tabela Purchases à tabela FactBuyingHabits. Nas linhas de clientes que compraram produtos que já compraram anteriormente, basta que você atualize a data de compra na tabela FactBuyingHabits. Essas operações de inserção e atualização podem ser executadas em uma única instrução usando MERGE.
O exemplo a seguir primeiro cria as tabelas Purchases e FactBuyingHabits e carrega nelas alguns dados de exemplo. O desempenho nas instruções MERGE melhora quando índices UNIQUE são criados na chave de junção, assim os índices clusterizados são criados gerando-se uma restrição PRIMARY KEY na coluna ProductID das duas tabelas.
Neste exemplo, Purchases contém as compras da semana de 21 de agosto de 2006. FactBuyingHabits contém as compras da semana anterior; normalmente esta tabela seria preenchida com linhas com datas bem mais anteriores.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
ProductID int, CustomerID int, PurchaseDate datetime,
CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
ProductID int, CustomerID int, LastPurchaseDate datetime,
CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
Agora as tabelas são populadas com os seguintes dados:
dbo.Purchases
ProductID CustomerID PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
ProductID CustomerID LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Observe que existem duas linhas Produto-Cliente em ambas as tabelas: o Cliente 11794 comprou o Produto 707 durante a semana atual, assim como na semana anterior, e o mesmo aconteceu com a compra do Produto 870 pelo Cliente 15160. Para essas linhas, atualizamos FactBuyingHabits com a data registrada para essas compras em Purchases usando a cláusula WHEN MATCHED THEN. Inserimos todas as outras linhas em FactBuyingHabits usando a cláusula WHEN NOT MATCHED THEN.
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
B. Executando operações UPDATE e DELETE
Os exemplos a seguir usam MERGE para atualizar diariamente a tabela ProductInventory no banco de dados de exemplo de AdventureWorks com base em pedidos processados na tabela SalesOrderDetail. Usando a seguinte instrução MERGE, a coluna Quantity da tabela ProductInventory foi atualizada pela subtração dos números de pedidos colocados a cada dia para cada produto. Se o número de pedidos de um produto fizer o estoque do produto cair até 0 ou abaixo de 0, a linha desse produto será excluída da tabela ProductInventory. Observe que a tabela de origem é agregada na coluna ProductID. Se isso não for feito, mais de um ProductID da tabela de origem pode combinar com a tabela alvo e fazer a instrução MERGE retornar um erro.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501'
C. Executando operações INSERT, UPDATE e DELETE
O exemplo a seguir usa MERGE para inserir, atualizar ou excluir linhas de uma tabela de destino com base nas diferenças em relação aos dados de origem. Considere uma pequena empresa com cinco departamentos, cada um com um gerente de departamento. A empresa decide reorganizar seus departamentos. Para implementar os resultados de re-organização na tabela de destino dbo.Departments, a instrução MERGE deve implementar as seguintes alterações:
Alguns departamentos existentes não serão alterados.
Alguns departamentos existentes terão novos gerentes.
Alguns departamentos serão criados.
Alguns departamentos não existirão depois da reorganização.
O código a seguir cria a tabela de destino dbo.Departments e a preenche com gerentes.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
As mudanças organizacionais a serem feitas nos departamentos são armazenadas na tabela de origem dbo.Departments_delta. O código a seguir cria e popula esta tabela:
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO
Por fim, para refletir a reorganização da empresa na tabela de destino, o seguinte código usa a instrução MERGE para comparar a tabela de origem dbo.Departments_delta com a tabela de destino dbo.Departments. O critério de pesquisa usado para essa comparação é definido na cláusula ON da instrução. Com base nos resultados da comparação, são tomadas as seguintes medidas:
Os departamentos que existem nas duas tabelas são atualizados na tabela de destino com novos nomes, gerentes ou ambos na tabela Departments. Se não houver nenhuma alteração, nada será atualizado. Isto é realizado pela cláusula WHEN MATCHED THEN.
Todos os departamentos de Departments_delta que não existem em Departments são inseridos em Departments. Isto é realizado pela cláusula WHEN NOT MATCHED THEN.
Todos os departamentos de Departments que não existem na tabela de origem Departments_delta são excluídos de Departments. Isso é feito pela cláusula WHEN NOT MATCHED BY SOURCE THEN.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;
Consulte também