This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, March 13, 2011

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 .



1 comment:

Sql Novice said...

Randhir,

I have a question and I'm new to SSIS and also, have no idea on VB.net but I sure can pick it up pretty quick! My question is that I currently have some files in a folder that need to be loaded to SQL Server, with no transformations (i.e. each file into a different table). All these different files need to go into different SQL Tables and I use an ODBC Connection. The data flow task simply is just a source and one destination each for the table and an error output(just in case). Now, all these source files are in a single folder and all the destination tables are the same db same schema. I was wondering if there is a way to create JUST ONE data flow task, use a for each loop to load all the different tables from the different sources. I wonder if it can be done at all and if I can does it have to be a VB script and a script task?