Post
Ask Question

Merging Table Data Using Merging Statement In MSSQL 2012

In this blog, we will learn how we can merge the table data using a merging statement. Before I share, I would like to explain in which cases we can use the merging statements. MERGE statement works best when two tables have a complex mixture of matching characteristics; for example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.
 
Step 1

Create "Products table", i.e., target table contains the changes which we are going to apply from the Source table.
  1. CREATE TABLE Products  
  2. (  
  3. ProductID INT PRIMARY KEY,  
  4. ProductName VARCHAR(100),  
  5. Rate MONEY  
  6. )   
  7. GO 
Step 2

Now, insert the data into Products Table.
  1. INSERT INTO Products  
  2. VALUES  
  3. (1, 'Tea', 10.00),  
  4. (2, 'Coffee', 20.00),  
  5. (3, 'Muffin', 30.00),  
  6. (4, 'Biscuit', 40.00)  
  7. GO  
The table will be something like this.

 
Step 3

Now, create UpdatedProducts table; i.e., Source table which the table data will be merged into with the target table; i.e., with Products table.
  1. CREATE TABLE UpdatedProducts  
  2. (  
  3. ProductID INT PRIMARY KEY,  
  4. ProductName VARCHAR(100),  
  5. Rate MONEY  
  6. )   
  7. GO 
Step 4

Now, insert data into UpdateProudcts table.
  1. INSERT INTO UpdatedProducts  
  2. VALUES  
  3. (1, 'Tea', 10.00),  
  4. (2, 'Coffee', 25.00),  
  5. (3, 'Muffin', 35.00),  
  6. (5, 'Pizza', 60.00)  
  7. GO   
  8.   
The result will be something like this.

 
Now, observe the two table that we created.
 
 

Step 5

Now, the Merge statement comes into the picture to make the changes which you want in your table. 
 
Syntax 
 
Initially, synchronize the target table with refreshed data from the source table. 
  1. MERGE Products AS TARGET  
  2. USING UpdatedProducts AS SOURCE   
  3. ON (TARGET.ProductID = SOURCE.ProductID)  
When records are matched, update the records if there is any change.
  1. WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName   
  2. OR TARGET.Rate <> SOURCE.Rate THEN   
  3. UPDATE SET TARGET.ProductName = SOURCE.ProductName,   
  4. TARGET.Rate = SOURCE.Rate  
When no records are matched, insert the incoming records from source table to target table.
  1. WHEN NOT MATCHED BY TARGET THEN   
  2. INSERT (ProductID, ProductName, Rate)   
  3. VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate) 
When there is a row that exists in the target table and same record does not exist in the source table, then delete this record from the target table.
  1. WHEN NOT MATCHED BY SOURCE THEN   
  2. DELETE 
In the below code $action specifies a column of type nvarchar(10) in the OUTPUT clause that returns one of the three values for each row: 'INSERT', 'UPDATE', or 'DELETE',  according to the action that was performed on that row. This is for our convenience to understand on which row the action is performed.
  1. OUTPUT $action,   
  2. DELETED.ProductID AS TargetProductID,   
  3. DELETED.ProductName AS TargetProductName,   
  4. DELETED.Rate AS TargetRate,   
  5. INSERTED.ProductID AS SourceProductID,   
  6. INSERTED.ProductName AS SourceProductName,   
  7. INSERTED.Rate AS SourceRate;   
  8. SELECT @@ROWCOUNT;  
  9. GO   
  1. select * from Products  
  2. select * from UpdatedProducts  
  3.   
  4. MERGE Products AS TARGET  
  5. USING UpdatedProducts AS SOURCE   
  6. ON (TARGET.ProductID = SOURCE.ProductID)   
  7. WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName   
  8. OR TARGET.Rate <> SOURCE.Rate THEN   
  9. UPDATE SET TARGET.ProductName = SOURCE.ProductName,   
  10. TARGET.Rate = SOURCE.Rate   
  11. WHEN NOT MATCHED BY TARGET THEN   
  12. INSERT (ProductID, ProductName, Rate)   
  13. VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)  
  14. WHEN NOT MATCHED BY SOURCE THEN   
  15. DELETE  
  16. OUTPUT $action,   
  17. DELETED.ProductID AS TargetProductID,   
  18. DELETED.ProductName AS TargetProductName,   
  19. DELETED.Rate AS TargetRate,   
  20. INSERTED.ProductID AS SourceProductID,   
  21. INSERTED.ProductName AS SourceProductName,   
  22. INSERTED.Rate AS SourceRate;   
  23. SELECT @@ROWCOUNT;  
  24. GO   
When we execute the  Products table, UpdatedProducts table, and the Merge statement, the output will be something like this.
 
 
 
In the above result, TargetProductID is the target column on which the action is performed.
Next Recommended Reading Resolve MERGE Statement issue on CDC Enabled Table