Best Practice To Use Merge Into In SQL Server

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

Start off by identifying the target table which will be used in the logic.
Next, identify the source table which will be used in the logic.
Determine the appropriate search conditions in the ON clause in order to match rows.
Specify logic when records are matched or not matched between the target and source i.e. 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 the 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 S
USING (SELECT MonthDate,TargetVal,TerritoryCode FROM @TargetType) T
ON (S.TerritoryCode = T.TerritoryCode AND S.MonthDate=T.MonthDate)
WHEN MATCHED THEN
UPDATE SET S.TargetVal = T.TargetVal,S.ModifiedDate=GETDATE()
WHEN NOT MATCHED THEN
INSERT (MonthDate,TargetVal,TerritoryCode,DoDate )
VALUES (E.MonthDate,E.TargetVal,E.TerritoryCode,GETDATE());