We often
have come across with a requirement where two tables need to be merged based on
different conditions and/or a condition of insert/update. Normally, these kinds
of requirements can be achieved by having multiple if/else conditions and
#temporary tables. However, to attain this in a single statement is also
possible by using MERGE statement. All you must do is: identify the source and
target tables + what needs to be done when records are matched/not matched.
Below is
the code snippet:
DECLARE
@MergeOutput TABLE
(
ActionType NVARCHAR(10),
DelCustomerId INT,
InstCustomerId INT,
DelCustomerName NVARCHAR(100),
InstCustomerName NVARCHAR(100),
DelCustomerAddress NVARCHAR(150),
InsCustomerAddress NVARCHAR(150)
);
MERGE
Customers AS target
USING (SELECT CustomerId,CustomerName,CustomerAddress
FROM
#Customers) AS SOURCE
ON (target.CustomerId = source.CustomerId)
WHEN MATCHED AND target.CustomerId IN (1,111,211) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET target.CustomerAddress
= source.CustomerAddress,
target.UpdatedBy = @UserId, target.UpdatedDateTime
= GetDATE()
WHEN NOT MATCHED THEN
INSERT (CustomerId,CustomerName,CustomerAddress,CreatedBy)
VALUES (source.CustomerId,source.CustomerName,source.CustomerAddress,@UserId);
OUTPUT
$action,
DELETED.CustomerId,
INSERTED.CustomerId,
DELETED.CustomerName,
INSERTED.CustomerName,
DELETED.CustomerAddress,
INSERTED.CustomerAddress
INTO
@MergeOutput;
SELECT * FROM Customers;
SELECT * FROM @MergeOutput;
Here
Customers is the Target table, and a temporary #Customers table is a Source
from where records will be matched.
Ideally
WHEN MATCHED will cover the UPDATE statement and NOT MATCHED will have the
INSERT statement as shown in the above example.
The output
clause returns the copy of data of the inserted/deleted & updated in the
table. The ActionType column contains the relevant action i.e. Insert/Update/Delete.
