MERGE Statement

Last Updated: October 26, 2023

The MERGE statement in SQL Server allows you to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of joining it with a source table or query. This is particularly useful for synchronizing data between two tables, such as loading data from a staging table into a production table.

The MERGE statement uses a common table expression (CTE) or a table variable as the source and a table or view as the target. The join condition specifies how rows from the source are matched with rows from the target.

Syntax


MERGE
    [ TOP ( expression ) [ PERCENT ] ] [ WITH ( < merge_option > [ , ...n ] ) ]
    <target_table> [ AS <alias> ]
USING
    <source_table> [ AS <alias> ]
ON
    <search_condition>
[
    WHEN MATCHED [ AND <additional_search_condition> ]
        THEN <merge_action> [ ...n ]
    WHEN NOT MATCHED [ BY TARGET ] [ AND <additional_search_condition> ]
        THEN <insert_statement> [ ...n ]
    WHEN NOT MATCHED BY SOURCE [ AND <additional_search_condition> ]
        THEN <delete_or_update_statement> [ ...n ]
]
[;]

<merge_option> ::=
      KEEP_IDENTITY
    | IGNORE_ROW_DUPLICATION
    | FAIL_ON_DUPLICATES

Parameters Explained

<target_table>: The table or view to be modified.
<source_table>: The table, view, or query that provides the data for comparison.
ON <search_condition>: The condition used to join the source and target tables. This is crucial for determining matches.
WHEN MATCHED: Specifies actions to take when a row in the source table matches a row in the target table based on the ON condition. You can specify UPDATE or DELETE here.
WHEN NOT MATCHED [BY TARGET]: Specifies actions to take when a row in the source table does not have a match in the target table. This clause is typically used for INSERT operations.
WHEN NOT MATCHED BY SOURCE: Specifies actions to take when a row in the target table does not have a match in the source table. This clause is typically used for UPDATE or DELETE operations on the target table.
<merge_action>: An UPDATE or DELETE statement.
<insert_statement>: An INSERT statement.

Example: Synchronizing Product Inventory

Suppose we have a Products table (target) and a StagingProducts table (source) containing updated inventory information. We want to update existing products and insert new ones.

Scenario Setup

Target Table: Products


CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    StockQuantity INT
);

INSERT INTO Products (ProductID, ProductName, StockQuantity) VALUES
(1, 'Laptop', 50),
(2, 'Keyboard', 150),
(3, 'Mouse', 200);

Source Table: StagingProducts


CREATE TABLE StagingProducts (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    StockQuantity INT
);

INSERT INTO StagingProducts (ProductID, ProductName, StockQuantity) VALUES
(1, 'Laptop', 45),      -- Update existing
(3, 'Mouse', 180),     -- Update existing
(4, 'Monitor', 75);     -- New product

The MERGE Statement


MERGE Products AS T  -- Target table
USING StagingProducts AS S -- Source table
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
    UPDATE SET
        T.ProductName = S.ProductName,
        T.StockQuantity = S.StockQuantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, StockQuantity)
    VALUES (S.ProductID, S.ProductName, S.StockQuantity);

Result

After executing the MERGE statement, the Products table will be:

ProductID	ProductName	StockQuantity
1	Laptop	45
2	Keyboard	150
3	Mouse	180
4	Monitor	75

Important Considerations

A MERGE statement must include at least one WHEN clause.
If the ON clause uses a column that is not part of a unique index or constraint, it may lead to multiple rows being matched, which can cause unexpected behavior or errors depending on the merge_option used.
The MERGE statement is a powerful tool for data synchronization and can significantly simplify complex data update scenarios.
Always test MERGE statements thoroughly in a development or staging environment before deploying them to production.

The MERGE statement enables you to conditionally INSERT, UPDATE, or DELETE rows in a target table. This can be based on the results of a join with a source table.

For more advanced scenarios and detailed explanations of merge_options, refer to the official SQL Server MERGE documentation.