SQL MERGE command is an advanced way of comparing data in two tables (Source and Destination). We compare records and based on match (or difference) we execute UPDATE, INSERT or DELETE according to the result of the table comparison. We will use this mainly with data sync between objects/systems or with ETL processes in environments like data warehouses or datamarts.
SQL MERGE statement Syntax and example
The syntax is follows:
MERGE Target_Table AS TARGETUSING Source_Table AS SOURCE ONWHEN MATCHED THENWHEN NOT MATCHED BY TARGET THENWHEN NOT MATCHED BY SOURCE THEN ;Imagine two tables we want to synchronize:
- dbo.Job_Position (DESTINATION) – target table located in data warehouse/data mart
- dbo.Job_Position_SourceSystem (SOURCE) table in the source system from which we are going to synchronize data
The synchronized field will be the employee position – “Employee_Position”. MERGE syntax is very intuitive and it will contain three parts:
- If the record exists both in the source and target destination we execute UPDATE (WHEN MATCHED)
- If the record does not exist in the target table we execute INSERT (NOT MATCHED)
- If the record does not exist in the source table but exists in the target table we execute DELETE (NOT MATCHED BY SOURCE)
MERGE dbo.Job_Position AS TARGETUSING dbo.Job_Position_SourceSystem AS SOURCEON TARGET.ID_JobPosition = SOURCE.ID_JobPositionWHEN MATCHED AND 1=1 THEN UPDATE SET TARGET.Employee_Position=SOURCE.Employee_PositionWHEN NOT MATCHED BY TARGET THENINSERT (ID_JobPosition, Employee_Position)VALUES (ID_JobPosition, Employee_Position)WHEN NOT MATCHED BY SOURCE THENDELETE;In case you do not understand why I wrote AND 1=1 into „WHEN MATCHED“ section – my purpose was to hint that you can define conditions in MATCHED parts
