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 table comparison.
We will use this mainly with data sync between objects/systems or with ETL processes in environments like data warehouse or data mart.
Syntax:
MERGE Target_Table AS TARGETUSING Source_Table AS SOURCE ONWHEN MATCHED THENWHEN NOT MATCHED BY TARGET THENWHEN NOT MATCHED BY SOURCE THEN ;SQL MERGE Command Example
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 source system from which we are going to synchronize data
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 source and target destination we execute UPDATE (WHEN MATCHED)
- If the record does not exist in target table we execute INSERT (NOT MATCHED)
- If the record does not exist in source table but exists in 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;Note: 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
