Best Practice To Use Merge Into In SQL Server

The SQL Server MERGE command combines the INSERT, UPDATE, and DELETE commands into a single statement. Here is how to get started with the use of MERGE in SQL Server:

Start by identifying the target table that will be used in the logic.
Next, identify the source table that will be used in the logic.
Determine the appropriate search conditions in the ON clause to match rows.
Specify the logic for determining when records are matched or not matched between the target and source, i.e., the comparison conditions.
For each of these comparisons, conditions code the logic. When matched, generally an update condition is used. When not matched, generally an insert or delete condition is used.

However, SQL Server provides a statement that allows you to perform three actions at the same time. The following shows the syntax of the MERGE statement:


MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

MERGE target_table USING source_table

ON merge_condition

WHEN MATCHED

THEN update_statement

WHEN NOT MATCHED

THEN insert_statement

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

Example


MERGE INTO DepotDataTarget AS S
USING (
    SELECT MonthDate, TargetVal, TerritoryCode
    FROM @TargetType
) AS T
ON (S.TerritoryCode = T.TerritoryCode AND S.MonthDate = T.MonthDate)

-- Action 1: When a record matches, update it.
WHEN MATCHED THEN
    UPDATE SET
        S.TargetVal = T.TargetVal,
        S.ModifiedDate = GETDATE()

-- Action 2: When a record is in the source but not the target, insert it.
WHEN NOT MATCHED BY TARGET THEN
    INSERT (MonthDate, TargetVal, TerritoryCode, DoDate)
    VALUES (T.MonthDate, T.TargetVal, T.TerritoryCode, GETDATE())

-- Action 3 (Your Request): When a record is in the target but not the source, delete it.
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;