Use of Merge Statement

Merge Statement :  Heard many times and but think today to explore it. This is a new introduced statement in SQL Server 2008. A very helpful statement for developers and DBA.

So what we can do with this statement. Let's explore:

Suppose we have to create a Interest calculation script, we have a table Clients with ClientID and Amount cloumn, and another table that will store the Interest Amount . Here is the schema:

IF OBJECT_ID('tempdb..#Clients') Is Not Null Drop Table #Clients
IF OBJECT_ID('tempdb..#ClientInterest') Is Not Null Drop Table #ClientInterest

Create Table #Clients
(
ClientID Int,
Amount Int
)

Insert Into #Clients
Values (101,50000), (102,70000),(103,65000),(104,100000),(105,45000)


Create Table #ClientInterest
(
ClientID Int,
Amount Int,
Interest Int
)

Insert Into #ClientInterest (ClientID, Amount)
Values (101,50000), (102,63000),(103,41000),(108,150000)

-----Before Merge Statement
Select * From #Clients
Select * From #ClientInterest

What we have to do before Interest calculation  :
1. Add new client to ClientInterest Table
2. If Client exist already in ClientInterest Table , update the Amount column with Clients table, because client can increase/decrease the Amount.
3. If Client does not exist in Clients table but exist in ClientInterest Table then delete that to Client table, because don't have to calculate the Interest  on clients whose business has been  closed .

Means both tables should be sync before calculating Interest.

To get this, need to write separate INSERT, UPDATE ,DELETE statements on earlier versions, but now
in SQL Server 2008  we can go ahead with single statement.

Here is the solution of above requirement :


-----Merge

Merge #ClientInterest AS Target
USING #Clients As Source
ON (Target.ClientID = Source.ClientID)
When Matched Then
Update Set Target.Amount = Source.Amount
When Not Matched Then
Insert (ClientID,Amount )
Values (Source.ClientID,Source.Amount )
When Not Matched By Source Then
Delete
OUTPUT $action, Inserted.*, Deleted.*;

-----After Merge Statement
Select * From #Clients
Select * From #ClientInterest



This statement will insert new client , update already existing, and delete if not Clients table but in ClientInterest table.

Other best stuff in this , can use the OUTPUT clause to see the action taken.

So can says , really helpful statement for us. Minimize code and save time,  I did not check the performance of this statement , but read on MSDN works faster than separate statements .