Introduction
This article gives an explanation about the MERGE statement in SQL server. Basically, the MERGE statement in SQL server joins the result set of target table to the result set of source table by using a common column to both tables and you can insert, update and delete at the same time in a single atomic statement, on the target table.
While you working with any data-driven applications, many developers face issues when they want to perform multiple Insert, Update and Delete statements. To overcome this kind of problem in SQL server there is an option to use the MERGE statement that allows you to do this all the statements at once.
Requirement
1) Introduction SQL Server MERGE Statement
2) Syntext of MERGE Statement in SQL server
2) Explain MERGE Statement in SQL server with Example.
The use of MERGE Statement in SQL server?
In SQL server MERGE Statement is used to perform multiple Insert, Update and Delete statements at the same time in a single atomic statement.
Consider the following scenario, suppose, you have two SQL table/result set called as the source table and target tables, and you need to insert/update/delete the records into the target table based on the values matched from the source table. There are three possible cases as I showed below.
INSERT
Suppose your source table has some records that do not exist into your target table. So, In this scenario, you need to insert that records that are available in the source table into your target table.
DELETE
Now, suppose your target table has some records that do not exist into your source table. So, In this scenario, you need to delete that records from your target table.
UPDATE
Now, suppose you have some records in your source table with the same keys as the records available in your target table. However, some of these records/rows have some different values in the non-key columns. now, In this scenario, you have to update the records in your target table with the values coming from your source table.
Syntext of MERGE Statement
MERGE Your_target_table USING Your_source_table ON Your_merge_condition WHEN MATCHED THEN Your_update_statement WHEN NOT MATCHED THEN Your_insert_statement WHEN NOT MATCHED BY SOURCE THEN DELETE;
So, let's start with an example so you can get more idea about the MERGE statement in SQL server.
Example
We will create two tables with the same column first is tblSales_Master_Target as Target table and tblSales_Master_Source as the source table. Now we will insert some of the dummy records for demonstration purpose.
Target Table
CREATE TABLE tblSales_Master_Target ( Sales_id INT PRIMARY KEY, Customer_name VARCHAR(255) NOT NULL, Product_name VARCHAR(255) NOT NULL, Amount FLOAT );
Insert Records into Target Table
INSERT INTO tblSales_Master_Target(Sales_id, Customer_name,Product_name, Amount) VALUES(1,'Nikunj Satasiya','Lenovo Laptop',45000), (2,'Karishna Patel','LG TV',20000), (3,'Hiren Dobariya','Mobile',20000), (4,'Vivek Ghadiya','MI TV',14999);
SELECT * FROM tblSales_Master_Target
Source Table
CREATE TABLE tblSales_Master_Source ( Sales_id INT PRIMARY KEY, Customer_name VARCHAR(255) NOT NULL, Product_name VARCHAR(255) NOT NULL, Amount FLOAT );
Insert Records into Source Table
INSERT INTO tblSales_Master_Source(Sales_id, Customer_name,Product_name, Amount) VALUES(1,'Nikunj Satasiya','Lenovo Laptop',45000), (3,'Hiren Dobariya','Mobile',20000), (4,'Vivek Ghadiya','MI TV',28500), (5,'Shreya Patel','Dell Laptop',33000), (6,'Mohit Patel','AC',25000);
SELECT * FROM tblSales_Master_Source
Now, we will marge target and source table and perform multiple Insert, Update and Delete statements using a common column to both tables.
MARGE Statment in SQL Server
MERGE tblSales_Master_Target tblTarget USING tblSales_Master_Source tblSource ON (tblSource.Sales_id = tblTarget.Sales_id) WHEN MATCHED THEN UPDATE SET tblTarget.Customer_name = tblSource.Customer_name, tblTarget.Product_name = tblSource.Product_name, tblTarget.Amount = tblSource.Amount WHEN NOT MATCHED BY TARGET THEN INSERT (Sales_id, Customer_name,Product_name, Amount) VALUES (tblSource.Sales_id, tblSource.Customer_name, tblSource.Product_name, tblSource.Amount) WHEN NOT MATCHED BY SOURCE THEN DELETE;
Explanation
As you can see in the above example there are two different tables target and source table with the same column. The target table contains 4 records with product and customers detail and the source table has 5 records. Some of the records are same in both table but some of the records are missing in the target table as well as some of the records that are available in the source table but not in the target table.
Look, Sales_id(2) is available in Target table but not in the source table so, Sales_id(2) should be removed from the target table and same as Sales_id(5) and Sales_id(6) is available in the source table so that records should be inserted in the target table. All other matched records should be updated in the target table based on the value comes from the source table for the particular sales record.
To archive, this kind of requirement SQL server provided a MARGE statement where you can do all the operations as I discussed above for INSERT records in the Target Table based on the source table for missing entries of sales record int the target table, as well as UPDATE matched records in the target table based on the value, comes from source table, and finally DELETE those entries from the target table which is not available in the source table but available in the target table.
Here, in the above example, we have used MARGE statement to do all the operation INSERT, UPDATE and DELETE in a single statement at once and marge both tables using the statement like MERGE tblSales_Master_Target and give alias of the table tblTarget with USING clause like USING tblSales_Master_Source and give alias of the table tblSource on the same key of both table like primary key of both table which is Sales_id. Now checked WHEN MATCHED means when matching any records in both table then we updating Target table based on value coming from the Source table so all the value will be updated in a target table based on source table values. Now, we checked for not matched value with target table using the WHEN NOT MATCHED BY TARGET then we inserting that entries into the target table based on value coming from the source table and finally check for the records which are not matched in the source table using WHEN NOT MATCHED BY SOURCE and delete these records from the
target table.
OUTPUT
Target Table Before/After
Summary
In this article, we learned what is MARGE statement? how to use MARGE statement in SQL server, The use of MARGE statement in SQL server, How we can do INSERT, UPDATE and DELETE operations in a single statement at the same time with an example.
Still, If you have any questions regarding MARGE statement you can post your questions as comments in the comment section, I am available here to resolve your queries and your questions.
Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.
Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.
Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.
If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com