Merge in SQL Server

MERGE (Transact-SQL)

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Syntax:

[ 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 ] ) ]    
;

WHEN MATCHED THEN <merge_matched>

Specifies that all rows of target_table that match the rows returned by <table_source> ON <merge_search_condition>, and satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

The MERGE statement can have at most two WHEN MATCHED clauses. If two clauses are specified, then the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first is not. If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action. If UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source>matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. The MERGE statement cannot update the same row more than once, or update and delete the same row.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that does not match a row in target_table, but does satisfy an additional search condition, if present. The values to insert are specified by the <merge_not_matched> clause. The MERGE statement can have only one WHEN NOT MATCHED clause.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Specifies that all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Only columns from the target table can be referenced in <clause_search_condition>.

When no rows are returned by <table_source>, columns in the source table cannot be accessed. If the update or delete action specified in the <merge_matched> clause references columns in the source table, error 207 (Invalid column name) is returned. For example, the clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 may cause the statement to fail because Col1 in the source table is inaccessible.

Example:

CREATE TABLE StudentDetails(StudentID INTEGER PRIMARY KEY,StudentName VARCHAR(15)
)GOINSERT INTO StudentDetailsVALUES(1,'SMITH')INSERT INTO StudentDetailsVALUES(2,'ALLEN')INSERT INTO StudentDetailsVALUES(3,'JONES')INSERT INTO StudentDetailsVALUES(4,'MARTIN')INSERT INTO StudentDetailsVALUES(5,'JAMES')GO

CREATE TABLE StudentTotalMarks(StudentID INTEGER REFERENCES StudentDetails,StudentMarks INTEGER)GOINSERT INTO StudentTotalMarksVALUES(1,230)INSERT INTO StudentTotalMarksVALUES(2,255)INSERT INTO StudentTotalMarksVALUES(3,200)GO-- Select from TableSELECT *FROM StudentDetails
GOSELECT *FROM StudentTotalMarks
GO

-- Merge StatementMERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sdON stm.StudentID = sd.StudentIDWHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks +25WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)VALUES(sd.StudentID,25);GO

-- Select from TableSELECT *FROM StudentDetails
GOSELECT *FROM StudentTotalMarks
GO

Using MERGE to perform UPDATE and INSERT operations on a target table by using a derived source table

-- 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;

 Inserting the results of the MERGE statement into another table

The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. The MERGE statement updates theQuantity column of the ProductInventory table in the AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail table. The example captures the rows that are updated and inserts them into another table that is used to track inventory changes.

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