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