Tuesday, December 5, 2017

Use of MERGE in SQL

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.

1 comment:

  1. thank you for providing this useful blog. I learned more from this site, which is among the best blogs ever. Anyone interested in learning about data science course in Mumbai can find information here. This site will also be very helpful for those who want to learn data science using MySQL and SQL with Python.

    ReplyDelete