How to use MERGE statement to Synchronize Data from Source into Target Table in SQL Server

SQL Server MERGE Statement to perform Insert, Update or Delete operations in a single statement
MERGE statement is used to perform Insert, Update, or Delete operations in a single statement.
The MERGE statement combines INSERT, DELETE, and UPDATE operations into one table. The MERGE statement allows you to join a data Source with a Target table or view, and then perform multiple actions against the Target based on the results of that join.

We can use the MERGE statement to perform the following operations:
Conditionally insert or update rows in a Target table.
If the row exists in the Target table, Update one or more columns; otherwise, Insert the data into a new row.
Insert, Update, or Delete rows in a Target table based on differences in the Source data to Synchronize data into Target.

The MERGE Syntax:

MERGE TargetTable
              Using SourceTable
              ON MergeCondition
WHEN MATCHED
             THEN UpdateStatement
WHEN NOT MATCHED BY TARGET
             THEN InsertStatement
WHEN NOT MATCHED BY SOURCE
OUTPUT $Action, Inserted.*, Deleted.*;

The MERGE clause specifies the Table or View that is the target of the Insert, Update, or Delete operations.
The USING clause specifies the data Source being joined with the Target.
The ON clause specifies the join conditions that determine where the Target and Source match.
The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
The OUTPUT clause returns a row for each row in the Target that is Inserted, Updated, or Deleted based on the $Action.
The MERGE Statement should terminate with a Semicolon.

Scenario :

Suppose we have the Target and Souce Tables as follows...

Select * From Tbl_Orders_Target

Select * From Tbl_Orders_Source

Now if we compare the Order_Ids of the Target against Source, we can notice few changes, based on that change we need to take the following actions:

a) The details of the Order_Id=1234 of the Target Table are modified in the Source which we need to Update in the Target.

b) The Order_Status of the Order_Id=5321 of the Target Table status has changed to 'In Active' in the Source, so that we need to Delete that record from the Target.

c) The details of the Order_Id=1237 of the Target Table is not available in the Source so that we need to Delete that record from the Target.

d) The details of the Order_Id=1236 of the Source Table is not available in the Target so that we need to Insert that record into the Target.

--Synchronize the Target Table with refreshed data from Source Table
MERGE Tbl_Orders_Target  AS TARGET
               USING Tbl_Orders_Source AS SOURCE
-- The Merge Condition between Target and Source
              ON (TARGET.Order_Id = SOURCE.OrderId)

--When records are matched(Merge Condition) and the OrderStatus in Source is 'In Active' Then Delete that record from Target
WHEN MATCHED AND (SOURCE.OrderStatus='In Active') THEN DELETE

--When records are matched(Merge Condition), update the records in Target if there is any change in Source
WHEN MATCHED AND
                     (TARGET.Prod_Name <> SOURCE.ProdName
                      OR TARGET.Order_Qnty <> SOURCE.OrderQnty
                      OR TARGET.Order_Status <> SOURCE.OrderStatus
                      OR TARGET.Order_Cost <> SOURCE.OrderCost)   THEN
      UPDATE SET       TARGET.Prod_Name = SOURCE.ProdName,
                     TARGET.Order_Qnty = SOURCE.OrderQnty,
      TARGET.Order_Status = SOURCE.OrderStatus,
                     TARGET.Order_Cost = SOURCE.OrderCost

--When no records are matched(Merge Condition), insert the incoming records from Source Table to Target Table
WHEN NOT MATCHED BY TARGET THEN
            INSERT (Order_Id, Prod_Name, Order_Qnty,Order_Status,Order_Cost)
            VALUES (SOURCE.OrderId, SOURCE.ProdName,  SOURCE.OrderQnty,SOURCE.OrderStatus,SOURCE.OrderCost)

--When there is a row that exists in Target Table and same record does not exist in Source Table then delete this record from Target Table
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 'INSERT', 'UPDATE', or 'DELETE' for each row according to the action that was performed on that row

OUTPUT $Action, Inserted.*, Deleted.*

;

--INSERTED.Order_Id AS SrcOrderId,
--INSERTED.Prod_Name AS SrcProdName,
--INSERTED.Order_Qnty AS SrcOrderQnty,
--INSERTED.Order_Status AS SrcOrderStatus,
--INSERTED.Order_Cost AS SrcOrderCost ,
--DELETED.Order_Id AS TgtOrderId,
--DELETED.Prod_Name AS TgtProdName,
--DELETED.Order_Qnty AS TgtOrderQnty,
--DELETED.Order_Status AS TgtOrderStatus,
--DELETED.Order_Cost AS TgtOrderCost
;
SELECT @@ROWCOUNT AS RecordsEffected;
GO

--Finally see the Output in Target Table
Select * From Tbl_Orders_Target
GO

----------------------------------------------------------

Output :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------