SQLINFO – We play with data and technology

Merge statement is a very powerful feature in T-SQL. I already know merge statement before, but when I worked with DIMITRI FURMAN (Microsoft SME) he usually use the MERGE Statement in his code. This helps me to know the scalability of the MERGE statement.

I will explain the MERGE Statement with a small scenario.

Consider a bank which has some existing loan options. The details are,

ID	LoanName	InterestRate
1	CarLoan	12.50
2	BikeLoan	10.50
3	HouseLoan	8.00
4	GoldLoan	12.00
6	FlatLoan	15.00

Now the bank has planned to change the interest rate of the existing loans and to include the some new loans. They also planned to stop some existing loans. Their new loan and their rates are

ID	LoanName	InterestRate
1	CarLoan	10.00
2	BikeLoan	8.00
3	HouseLoan	7.00
4	GoldLoan	12.00
5	AgricultureLoan	5.00

 

After Implementing the new loan structure, the loan details are ,

ID	LoanName	InterestRate
1	CarLoan	10.00
2	BikeLoan	8.00
3	HouseLoan	7.00
4	GoldLoan	12.00
5	AgricultureLoan	5.00

 

I think you are clear about the scenario. Here we need to so some INSERT, UPDATE, and DELETE operation when we come to T-SQL. The Operations are ,

1. Inserting the New loan into the existing table.
2. Updating the new Loan interest rates to existing table.
3. Deleting the dropped loan from the existing table.

Can we do this operation in a single step? Rather than doing it separately? The answer is yes.We can achieve this operation in a single pass with the help of Merge statement.Merge statement will Improve the performance.

I am going to Implement the business scenario with the help of  T-SQL.

 

--  Creating the table to hold the existing Bank Loan deatils

Create Table BankLoan
( ID int Primary key,
LoanName varchar(50),
InterestRate decimal(5,2)
)

--Inserting the data with existing Bank Loan deatils

Insert into BankLoan
Select 1,'CarLoan',12.5
union all
Select 2,'BikeLoan',10.5
union all
select 3,'HouseLoan',8
union all
select 4,'GoldLoan', 12
union all
select 6,'FlatLoan',15

--  Creating the table to hold the revised Bank Loan deatils

Create Table BankLoan_Revised
( ID int Primary key,
LoanName varchar(50),
InterestRate decimal(5,2)
)

--Inserting the data with revised Bank Loan deatils

Insert into BankLoan_Revised
Select 1,'CarLoan',10
union all
Select 2,'BikeLoan',8
union all
select 3,'HouseLoan',7
union all
select 4,'GoldLoan', 12
union all
select 5,'AgricultureLoan',5

--using Merge statement to  Implementing Insertion,updation,
--Deletion in a single pass

Merge BankLoan as TARGET
USING BankLoan_Revised as SOURCE
ON TARGET.ID = SOURCE.ID --joining condition
When MATCHED AND TARGET.LOANNAME = SOURCE.LOANNAME
THEN
UPDATE SET TARGET.InterestRate = SOURCE.InterestRate --updating target table
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ID,LOANNAME,INTERESTRATE) --Inserting data if no record in the target
VALUES (SOURCE.ID,SOURCE.LOANNAME,SOURCE.INTERESTRATE)
When Not matched by source
Then
Delete -- Deleting data if no data is matched.
-- Displaying which action is performed and which records are affected
Output $action,
Deleted.ID as DeletedID,
Deleted.LoanName as DeletedLoanName,
Deleted.Interestrate as DeletedInterestrate,
getdate() ;

GO
Select * from BankLoan;

Result :

Select * from BankLoan;

Result of output clause: