Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 11 May 2015

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!!!!!!!