Exportar (0) Imprimir
Expandir Tudo

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).

É 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.

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 AdventureWorks2008R2;
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 AdventureWorks2008R2 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 AdventureWorks2008R2;
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 reorganizaçã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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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;    


Isso foi útil para você?
(1500 caracteres restantes)
Agradecemos os seus comentários

Contribuições da comunidade

ADICIONAR
Mostrar:
© 2014 Microsoft