Hello Friends, today I am back after a long time with a new article on T-SQL. Today’s topic is Merge statement which was introduced in SQL 2008 version. Using merge statement, we can perform Insert, update or delete operations in a single statement. Lets take an example and see how merge statement works. First of all we will create two tables named “Employee” and “EmpAddress” and insert few rows into both tables, using below SQL script.
Create table Employee(Eid int,Name Varchar(50),Dept varchar(50))
Insert into Employee Values(101,’Neeraj Yadav’,’IT’)
Insert into Employee Values(102,’Ashish Sharma’,’HR’)
Insert into Employee Values(103,’Amit Tiwari’,’Marketing’)
Insert into Employee Values(104,’Raj Yadav’,’ADMIN’)
Insert into Employee Values(105,’Praveen Kumar’,’Accounts’)
Create table EmpAddress(Eid int,Address varchar(50),Dept varchar(50))
Insert into EmpAddress Values(101,’Faridabad’,’Information technology’)
Insert into EmpAddress Values(103,’Gurgaon’,’MkT’)
Insert into EmpAddress Values(105,’Noida’,’ACC’)
Now I want to join these two tables based on “Eid” column which is common in both tables and insert details of those employees which are not present in “EmpAddress” table and update the “Dept” column of “EmpAddress” with “Dept” column of “Employee” table.
MERGE EmpAddress AS Target
USING (SELECT Eid, Name, Dept FROM Employee) AS Source
ON (Target.Eid = Source.Eid )
WHEN MATCHED THEN
UPDATE SET Target.Dept = Source.Dept
WHEN NOT MATCHED BY TARGET THEN
INSERT (Eid, Dept)
VALUES (Source.Eid, Source.Dept);
OUTPUT $action, Inserted.*, Deleted.*;
Lets execute the above statement and see the results.
Look at the result, first column “@action” tells us that two rows has been inserted in “EmpAddress” table and three rows has been updated. In merge statement there are two clauses first is denoted by “When matched” and other one is denoted by “When not Matched”. We write Insert or delete statement according to our need. In this article I have taken example of update and Insert. You can try delete by your own. Last line of Merge statement is “Output @action, Inserted.*,deleted.*;” , which is used to see the output. You can remove the last line but merge statement ends with a semi column. So after removing the last line, just put a semi column.
I hope you will find this article useful. Please let me know, in case you have any question.
802 total views, 2 views today