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