SQL SERVER – MERGE STATEMENT – SIMPLE EXAMPLE

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: