Using Merge in Sql Server 2008

Credits:

I came to know about merge through my friend, my colleague and my partner Gireesh. Thanks for the effort he put in.

 

The scenario for using Sql Server 2008 Merge?

Declare @IdsOld Table 
(
Id Int, 
Name Varchar(50)
)
Declare @IdsNew Table 
(
Id Int, 
Name Varchar(50)
)

Insert into @IdsOld(Id, Name)
Values(1, 'To Update')
Insert into @IdsOld(Id, Name)
Values(0, 'To Delete')

Select * from @IdsOld


Insert into @IdsNew(Id, Name)
Values(1, 'Updated Value')
Insert into @IdsNew(Id, Name)
Values(2, 'To Insert')

Select * from @IdsNew

Merge @IdsOld As Target
Using (Select * from @IdsNew) As Source
On (Target.Id = source.Id)
When Matched Then Update Set Target.Name = Source.Name
WHEN NOT MATCHED BY Target THEN Insert(Id, Name) Values(Source.Id, Source.Name)
WHEN NOT MATCHED BY SOURCE THEN DELETE;


select * from @IdsNew

So in cases when there is a need for multiple insert, update & delete scenarios you can use merge

Update:

a better example can be found at http://pramod-pv.blogspot.com/2011/07/sql-server-2008-merge-sample.html