MERGE (Transact-SQL)

Sintaxe

[ WITH <common_table_expression> [,...n] ]
MERGE 
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
        USING <table_source> 
        ON <merge_search_condition>
        [ WHEN MATCHED [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
            THEN <merge_not_matched> ]
        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
            THEN <merge_matched> ] [...n ]
        [ <output_clause> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

<target_table> ::=
{ 
    [ database_name . schema_name . | schema_name . ]
    target_table
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
            [ WITH ( table_hint [ [ , ]...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
            [ ( bulk_column_alias [ ,...n ] ) ] 
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML <openxml_clause> 
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | <joined_table> 
    | <pivoted_table> 
    | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
    { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                                              | field_name = expression }
                                              | method_name ( argument [ ,...n ] ) }
     }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

<merge_not_matched>::=
{
        INSERT [ ( column_list ) ] 
            { VALUES ( values_list )
            | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
        { [ NOT ] <predicate> | ( <search_condition> ) } 
        [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
    ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
        [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
            [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
        { <column_name> | scalar_expression } 
                [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
        { DELETED | INSERTED | from_table_name } . { * | column_name }
      | $action

Argumentos

• WITH <common_table_expression>
  Especifica a exibição ou o conjunto de resultados nomeado temporário, também conhecido como expressão de tabela comum, definido dentro do escopo da instrução MERGE. O conjunto de resultados é derivado de uma consulta simples e é referido pela instrução MERGE. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

• TOP ( expression ) [ PERCENT ]
  Especifica o número ou a porcentagem de linhas que são afetadas. expression pode ser um número ou uma porcentagem das linhas. As linhas referenciadas na expressão TOP não são organizadas em nenhuma ordem. Para obter mais informações, consulte TOP (Transact-SQL).

  A cláusula TOP é aplicada depois que toda a tabela de origem e toda a tabela de destino estiverem unidas e as linhas unidas, que não são qualificadas para uma ação de inserção, atualização ou exclusão são removidas. A cláusula TOP ainda reduz o número de linhas unidas para o valor especificado, e as ações de inserção, atualização ou exclusão são aplicadas às linhas unidas restantes de uma forma não ordenada. Isto é, não há nenhuma ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN. Por exemplo, especificar TOP (10) afeta 10 linhas. Dessas linhas, 7 podem ser atualizadas e 3 inseridas ou 1 pode ser excluída, 5 atualizadas e 4 inseridas etc.

  Como a instrução MERGE executa um exame completo das tabelas de origem e de destino, o desempenho de E/S pode ser afetado ao usar a cláusula TOP para modificar uma tabela grande criando vários lotes. Neste cenário, é importante garantir que todos os lotes sucessivos destinem novas linhas. Para obter mais informações, consulte Otimizando o desempenho da instrução MERGE.

• database_name
  É o nome do banco de dados no qual target_table está localizado.

• schema_name
  É o nome do esquema a que target_table pertence.

• target_table
  É a tabela ou a exibição com a qual as linhas de dados em <table_source> são comparadas com base em <clause_search_condition>. target_table é o destino de qualquer operação de inserção, atualização ou exclusão especificada pelas cláusulas WHEN da instrução MERGE.

  Se target_table for uma exibição, qualquer ação nela deverá atender às condições para atualizar exibições. Para obter mais informações, consulte Modificando dados por meio de uma exibição.

  target_table não pode ser uma tabela remota. target_table não pode ter nenhuma regra definida nela.

• [ AS ] table_alias
  É um nome alternativo usado para fazer referência a uma tabela.

• USING <table_source>
  Especifica a fonte de dados que é correspondida com as linhas de dados em target_table com base em <merge_search condition>. O resultado dessa correspondência define as ações das cláusulas WHEN da instrução MERGE. <table_source> pode ser uma tabela remota ou uma tabela derivada que acessa tabelas remotas.

  <table_source> pode ser uma tabela derivada que usa o Transact-SQLconstrutor de valor de tabela para construir uma tabela especificando várias linhas.

  Para obter mais informações sobre sintaxe e argumentos dessa cláusula, consulte FROM (Transact-SQL).

• ON <merge_search_condition>
  Especifica as condições nas quais <table_source> é unido a target_table para determinar onde há correspondência entre eles.

  Cuidado

  É importante especificar apenas as colunas da tabela de destino que são usadas para fins de correspondência. Isto é, especifique as colunas da tabela de destino que são comparadas à coluna correspondente da tabela de origem. Não tente melhorar o desempenho de uma consulta filtrando linhas na tabela de destino na cláusula ON ao especificar, por exemplo, AND NOT target_table.column_x = value. Isso pode retornar resultados inesperados e incorretos.

• WHEN MATCHED THEN <merge_matched>
  Especifica que todas as linhas de target_table que correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer critério de pesquisa adicional são atualizadas ou excluídas de acordo com a cláusula <merge_matched>.

  A instrução MERGE pode ter, no máximo, duas cláusulas WHEN MATCHED. Se duas cláusulas forem especificadas, a primeira deverá ser acompanhada por uma cláusula AND <search_condition>. Para qualquer linha especificada, a segunda cláusula WHEN MATCHED só será aplicada se a primeira não for. Se houver duas cláusulas WHEN MATCHED, uma delas deverá especificar uma ação UPDATE e a outra, uma ação DELETE. Se UPDATE for especificada na cláusula <merge_matched>, e mais de uma linha de <table_source> corresponder a uma linha em target_table com base em <merge_search_condition>, o SQL Server retornará um erro. A instrução MERGE não pode atualizar a mesma linha mais de uma vez ou atualizar e excluir a mesma linha.

• WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
  Especifica que uma linha é inserida em target_table para cada linha retornada por <table_source> ON <merge_search_condition> que não corresponde a uma linha em target_table, mas atende a um critério de pesquisa adicional, se houver. Os valores a serem inseridos são especificados pela cláusula <merge_not_matched>. A instrução MERGE pode ter apenas uma cláusula WHEN NOT MATCHED.

• WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
  Especifica que todas as linhas de target_table que não correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer critério de pesquisa adicional são atualizadas ou excluídas de acordo com a cláusula <merge_matched>.

  A instrução MERGE pode ter, no máximo, duas cláusulas WHEN NOT MATCHED BY SOURCE. Se forem especificadas duas cláusulas, a primeira deverá ser acompanhada por uma cláusula AND <clause_search_condition>. Para qualquer linha especificada, a segunda cláusula WHEN NOT MATCHED BY SOURCE só será aplicada se a primeira não for. Se houver duas cláusulas WHEN NOT MATCHED BY SOURCE, uma delas deverá especificar uma ação UPDATE e a outra, uma ação DELETE. Somente as colunas da tabela de destino podem ser referenciadas na <clause_search_condition>.

  Quando nenhuma linha é retornada através de <table_source>, as colunas na tabela de destino não podem ser acessadas. Se a ação de atualização ou exclusão especificada na cláusula <merge_matched> referenciar colunas na tabela de origem, o erro 207 (Nome de coluna inválido) será retornado. Por exemplo, a cláusula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 pode fazer com que a instrução falhe porque Col1 não pode ser acessado na tabela de origem.

• AND <clause_search_condition>
  Especifica qualquer critério de pesquisa válido. Para obter mais informações, consulte Critério de pesquisa (Transact-SQL).

• <table_hint_limited>
  Especifica uma ou mais dicas de tabela que são aplicadas à tabela de destino para cada uma das ações de inserção, atualização ou exclusão executadas pela instrução MERGE. A palavra-chave WITH e os parênteses são necessários.

  NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).

  A especificação da dica TABLOCK em uma tabela que é o destino de uma instrução INSERT tem o mesmo efeito que a especificação da dica TABLOCKX. Um bloqueio exclusivo é obtido na tabela. Quando FORCESEEK é especificada, ela é aplicada a uma instância implícita da tabela de destino unida à tabela de origem.

  Cuidado
  Especificar READPAST com WHEN NOT MATCHED [ BY TARGET ] THEN INSERT pode resultar em operações INSERT que violam restrições UNIQUE.

• INDEX ( index_val [ ,...n ] )
  Especifica o nome ou a identificação de um ou mais índices em uma tabela de destino para a execução de uma junção implícita com a tabela de origem. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

• <output_clause>
  Retorna uma linha para cada linha que é atualizada, inserida ou excluída em target_table, sem nenhuma ordem específica. Para obter mais informações sobre os argumentos dessa cláusula, consulte cláusula OUTPUT (Transact-SQL).

• OPTION ( <query_hint> [ ,...n ] )
  Especifica que dicas do otimizador são usadas para personalizar o modo como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

• <merge_matched>
  Especifica a ação de atualização ou exclusão que é aplicada a todas as linhas de target_table que não correspondem às linhas retornadas por <table_source> ON <merge_search_condition> e atendem a qualquer critério de pesquisa adicional.

  • UPDATE SET <set_clause>
    Especifica a lista de nomes de colunas ou de variáveis que devem ser atualizados na tabela de destino e os valores que devem ser usados na atualização.

    Para obter mais informações sobre os argumentos dessa cláusula, consulte UPDATE (Transact-SQL). Não é permitido definir o mesmo valor de uma coluna para uma variável.

  • DELETE
    Especifica que as linhas que correspondem a linhas em target_table são excluídas.

• <merge_not_matched>
  Especifica os valores a serem inseridos na tabela de destino.

  • ( column_list )
    É uma lista de uma ou mais colunas da tabela de destino na qual os dados devem ser inseridos. As colunas devem ser especificadas como um nome de parte única. Caso contrário, a instrução MERGE falhará. column_list deve estar entre parênteses e delimitado por vírgulas.

  • VALUES ( values_list )
    É uma lista de constantes, variáveis ou expressões separadas por vírgulas que retorna valores a serem inseridos na tabela de destino. As expressões não podem conter uma instrução EXECUTE.

  • DEFAULT VALUES
    Força a linha inserida a conter os valores padrão definidos para cada coluna.

  Para obter mais informações sobre essa cláusula, consulte INSERT (Transact-SQL).

• <search condition>
  Especifica os critérios de pesquisa usados para especificar <merge_search_condition> ou <clause_search_condition>. Para obter mais informações sobre os argumentos dessa cláusula, consulte Critério de pesquisa (Transact-SQL).

Comentários

Pelo menos uma das três cláusulas MATCHED devem ser especificadas, mas elas podem ser especificadas em qualquer ordem. Uma variável não pode ser atualizada mais de uma vez na mesma cláusula MATCHED.

Qualquer ação de inserção, atualização ou exclusão especificada na tabela de destino pela instrução MERGE é limitada pelas restrições definidas nela, incluindo qualquer restrição de integridade referencial em cascata. Se IGNORE_DUP_KEY for definida como ON em qualquer índice exclusivo na tabela de destino, MERGE ignorará essa definição.

A instrução MERGE requer um ponto-e-vírgula (;) como terminador de instrução. O erro 10713 ocorre quando uma instrução MERGE é executada sem o terminador.

Quando usada depois de MERGE, @@ROWCOUNT (Transact-SQL) retorna o número total de linhas inseridas, atualizadas e excluídas ao cliente.

MERGE é uma palavra-chave totalmente reservada quando o nível de compatibilidade de banco de dados é definido como 100. A instrução MERGE está disponível abaixo dos níveis de compatibilidade de banco de dados 90 e 100. No entanto, a palavra-chave não é totalmente reservada quando o nível de compatibilidade de banco de dados está definido como 90.

Implementação de gatilho

Permissões

Requer a permissão SELECT na tabela de origem e a permissão INSERT, UPDATE ou DELETE na tabela de destino. Para obter informações adicionais, consulte a seção Permissões nos tópicos SELECT, INSERT, UPDATE e DELETE.

Exemplos

A. Usando MERGE para executar operações INSERT e UPDATE em uma tabela em uma única instrução

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.   
    IF (@@ROWCOUNT = 0 )
    BEGIN
        INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN   
        INSERT (UnitMeasureCode, Name)
        VALUES (source.UnitMeasureCode, source.Name)
        OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
-- Cleanup 
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
Go

B. Usando MERGE para executar as operações UPDATE e DELETE em uma tabela em uma única instrução

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. Usando MERGE para executar as operações UPDATE e INSERT em uma tabela de destino com uma tabela de origem derivada

USE AdventureWorks;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
    UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. Inserindo os resultados da instrução MERGE em outra tabela

USE AdventureWorks;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty 
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty) 
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO

Consulte também

Referência

Conceitos