Merge Statement in SQL 2008

Merge Command:- 

Merge command is a new feature introduced in the SQL Server 2008. 

• It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.)

•  It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.

The Syntax of the Merge command is given below:-


MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ] (UPDATE Target table from Source)
[WHEN NOT MATCHED [BY TARGET]
THEN  ](INSERT Target table from Source)
[WHEN NOT MATCHED BY SOURCE
THEN  ];(DELETE Target table)

; semi colon is important in end of merget statement.


select 's' source,* from Import_Employee


select 't' target,* from employee

MERGE employee AS t

USING Import_Employee as s

on t.EmployeeNumber=s.empno

When matched then 

update set T.Firstname=s.Firstname,T.Lastname=s.Lastname

When not matched by target then

insert(EmployeeNumber,firstname,lastname)

values(s.empno,s.firstname,s.lastname)

when not matched by source then

delete;

Writing Merge Command in SQL Server 2008

After Executed Merge command See the results

Thanks for reading!!!!!!!