Power of Merge Statement in Sql Sever 2008

Merge Statement

The SQL Server 2008 provide a new and very importent feature that is Merge Statement.

Using the Merge, you can perform many statement like (Insert, Update, Delete) in a single statement.

To implement Merge statement, the following WHEN clauses are required:

- WHEN MATCHED THEN [INSERT, UPDATE, DELETE]
- WHEN NOT MATCHED [BY TARGET] THEN [INSERT, UPDATE, DELETE]
- WHEN NOT MATCHED BY SOURCE THEN [INSERT, UPDATE, DELETE]

I am explaining a real time problem which is easily handeled by Merge Statment.

EX :
------

Suppose you have a Customer table in your databse like

declare @Customer table(CustID int,CustName varchar(10),PhoneNo int)

insert into @Customer(CustID,CustName,PhoneNo)
values(1,'C1',101),(2,'C2',102),(3,'C3',103),(4,'C4',104),(5,'C5',105)

select * from @Customer


And you get a new Customer Table is like this

declare @CustomerNew table(CustID int,CustName varchar(10),PhoneNo int)

insert into @CustomerNew(CustID,CustName,PhoneNo)
values(2,'C2',1002),(4,'C4',1004),(6,'C6',1006)

select * from @CustomerNew


Task - 1

And you are assign a task that In Customer table some of Customers phonenumber has been changed and some are new customers then you need to update customer phonenumber in your customer table and also insert new customer details in customer table from New Customer Table.

Then generally before MS SQL 2008, you need to follow 2 steps : 1 for Update and 1 for Insert. But using New Merge Statement you can do this in a single statemnet like :

;merge @Customer as T
using @CustomerNew as S
on (T.CustID = S.CustID)
when matched then
update set T.PhoneNo = S.PhoneNo
when not matched then
insert(CustID,CustName,PhoneNo) values(S.CustID,S.CustName,S.PhoneNo)

select * from @Customer


Task - 2

And you are assign an another task that In Customer table some of Customers phonenumber has been changed and some are new customers then you need to update customer phonenumber in your customer table and also insert new customer details in customer table from New Customer Table and delete those customers which are not in New Customer Table.

Then generally before MS SQL 2008, you need to follow 3 steps : 1 for Update and 1 for Insert and 1 for Delete. But using New Merge Statement you can do this in a single statemnet like :

;merge @Customer as T
using @CustomerNew as S
on (T.CustID = S.CustID)
when matched then
update set T.PhoneNo = S.PhoneNo
when not matched then
insert(CustID,CustName,PhoneNo) values(S.CustID,S.CustName,S.PhoneNo)
when not matched by source then
delete;

select * from @Customer

Regards
Manish