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,
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
After Implementing the new loan structure, the loan details are ,
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 ,
- Inserting the New loan into the existing table.
- Updating the new Loan interest rates to existing table.
- 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;
Select * from BankLoan;
Result of output clause: