SQL Server MERGE Statement

SQL Server MERGE statement combines INSERT, UPDATE, and DELETE operations into a single transaction.

MERGE in SQL Server

The MERGE statement in SQL provides a convenient way to perform INSERT, UPDATE, and DELETE operations together, which helps handle the large running databases. But unlike INSERT, UPDATE, and DELETE statements MERGE statement requires a source table.

As we know that the MERGE in SQL requires two tables: one is the target table on which we want to perform INSERT, UPDATE, and DELETE operations, and the other one is the source table which contains the new modified and correct data for the target table and is compared with the actual target table to modify it.

Syntax

The MERGE statement syntax is:

MERGE INTO target_table
USING source_table
ON merge_condition
WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...)
WHEN NOT MATCHED BY TARGET THEN DELETE;

SQL Server MERGE Statement Examples

Let's look at an example of the MERGE statement in SQL Server. First, let's create two tables PRODUCT_LIST (target table) and UPDATED_LIST (source table)

PRODUCT_LIST Table:

 UPDATED_LIST Table:

Query:

MERGE PRODUCT_LIST AS TARGET
USING UPDATED_LIST AS SOURCE
ON (TARGET.P_ID = SOURCE.P_ID)
WHEN MATCHED AND (TARGET.P_NAME <> SOURCE.P_NAME OR TARGET.P_PRICE <> SOURCE.P_PRICE)
THEN UPDATE SET TARGET.P_NAME = SOURCE.P_NAME, TARGET.P_PRICE = SOURCE.P_PRICE
WHEN NOT MATCHED BY TARGET
THEN INSERT (P_ID, P_NAME, P_PRICE)
     VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Output

References - MERGE - docs.microsoftMERGE - docs.oracle