Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 12 June 2014

Merge Statement in SQL 2008

Merge Statement in SQL 2008

Suppose we have the following requirements:-

 1) If a empno is exists in both the table then the corresponding values of the column Fname and Lname for the empno in the table Employee is updated by the corresponding values of the column firstname and lastname of the Empno from the source table Import_Employee . 

2) If there is no corresponding empno in the Target table Employee for a empno of the table Import_Employee, then the data from the Source table is inserted into the target table for that empno. 

3) If there exists a records for a empno in the Target table whose corresponding records are not in the Source table, these these record should be deleted from the target table. Therefore, the condition on which the target table and Source table is joined will be the Empno.

The Syntax of the Merge command is given below:- 

MERGE [AS TARGET]
 USING [AS SOURCE] ON [WHEN MATCHED THEN ] 
[WHEN NOT MATCHED [BY TARGET] THEN ] 
[WHEN NOT MATCHED BY SOURCE THEN ];

MERGE Employeetest AS TARGET 
USING Import_Employee AS SOURCE 
ON TARGET.empno=SOURCE.empno 
WHEN MATCHED THEN update set TARGET.fname=SOURCE.firstname, TARGET.lname = SOURCE.lastname 
WHEN NOT MATCHED BY TARGET THEN 
insert(empno,fname,lname) values(SOURCE.empno,SOURCE.firstname,SOURCE.lastname)
 WHEN NOT MATCHED BY SOURCE THEN Delete;

  Select * from Import_Employee--Source
  Select * from Employeetest--------Target