استفاده از دستور Merge در دیتابیس

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;

--Synchronize the target table with refreshed data from source table
MERGE target_table AS TARGET
USING source_table AS SOURCE 
ON (TARGET.Id = SOURCE.Id) 

--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.Name <> SOURCE.Name OR TARGET.Code <> SOURCE.Code 
THEN UPDATE SET TARGET.Name = SOURCE.Name, TARGET.Code = SOURCE.Code 

--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (Id, Name, Code) VALUES (SOURCE.Id, SOURCE.Name, SOURCE.Code)

--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 

--$action specifies a column of type nvarchar(10) in the OUTPUT clause that returns 
--one of three values for each row: 'INSERT', 'UPDATE', 'DELETE' according to the action that was performed on that row
OUTPUT $action, 
DELETED.Id AS TargetId, 
DELETED.Name AS TargetName, 
DELETED.Code AS TargetCode, 
INSERTED.Id AS SourceId, 
INSERTED.Name AS SourceName, 
INSERTED.Code AS SourceCode;