T-SQL MERGE Statement

MERGE Statement

The MERGE statement is a powerful T-SQL construct that allows you to perform conditional INSERT, UPDATE, or DELETE operations on a target table based on the results of joining it with a source table. It's often referred to as an "upsert" operation (update or insert).

Syntax

MERGE target_table AS T
USING source_table AS S
ON T.merge_column = S.merge_column
WHEN MATCHED THEN
    UPDATE SET
        T.column1 = S.column1,
        T.column2 = S.column2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (column1, column2, column3)
    VALUES (S.column1, S.column2, S.column3)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
                

Key Components

• MERGE target_table AS T: Specifies the table that will be modified.
• USING source_table AS S: Specifies the table or query that provides the data for comparison and operations.
• ON T.merge_column = S.merge_column: Defines the join condition used to match rows between the target and source.
• WHEN MATCHED THEN ...: Specifies actions to take when a row from the source finds a matching row in the target based on the ON condition. This can include UPDATE or DELETE.
• WHEN NOT MATCHED BY TARGET THEN ...: Specifies actions to take when a row from the source does not have a matching row in the target. Typically used for INSERT.
• WHEN NOT MATCHED BY SOURCE THEN ...: Specifies actions to take when a row in the target does not have a matching row in the source. This is used for DELETE operations (to remove rows from the target that are no longer present in the source). This clause is optional.

When to Use MERGE

• Synchronizing data between two tables.
• Loading data from staging tables into production tables where records might exist or need to be added.
• Implementing complex conditional updates based on source data.

-- Target table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Stock INT
);

-- Source table (e.g., data from an external feed)
CREATE TABLE StagingProducts (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    NewStock INT
);

-- Populate with some sample data
INSERT INTO Products (ProductID, ProductName, Stock) VALUES
(1, 'Laptop', 50),
(2, 'Keyboard', 100);

INSERT INTO StagingProducts (ProductID, ProductName, NewStock) VALUES
(1, 'Laptop Pro', 45), -- Update existing
(3, 'Mouse', 200);      -- Insert new

-- MERGE statement to synchronize
MERGE Products AS T
USING StagingProducts AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
    UPDATE SET
        T.ProductName = S.ProductName,
        T.Stock = S.NewStock
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Stock)
    VALUES (S.ProductID, S.ProductName, S.NewStock);

-- After the MERGE, the Products table will be:
-- ProductID | ProductName  | Stock
-- -----------|--------------|-------
-- 1          | Laptop Pro   | 45
-- 2          | Keyboard     | 100
-- 3          | Mouse        | 200
                    

-- Assume StagingProducts no longer contains ProductID 2
DELETE FROM StagingProducts WHERE ProductID = 2;

MERGE Products AS T
USING StagingProducts AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
    UPDATE SET
        T.ProductName = S.ProductName,
        T.Stock = S.NewStock
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Stock)
    VALUES (S.ProductID, S.ProductName, S.NewStock)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- After this MERGE, ProductID 2 will be deleted from Products
                    

Considerations

• The MERGE statement must include at least one WHEN MATCHED or WHEN NOT MATCHED BY TARGET clause.
• If WHEN NOT MATCHED BY SOURCE is used, it should be the last clause.
• Performance can be sensitive to the join condition and indexing on both tables.
• Multiple WHEN MATCHED clauses are not allowed.
• Use output clauses ($action) to track changes made by the MERGE statement.