EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development SQL Tutorials T-SQL merge
Secondary Sidebar
Software Testing Interview Questions

What is JavaScript?

WordPress vs Wix

Web Services Interview Questions

Spring framework Interview Questions

Orphaned Case Java

T-SQL merge

T-SQL merge

Introduction to T-SQL merge

T-SQL merge is a statement in T-SQL that was established in SQL server 2008 edition. It provides flexibility to database programmers to clarify their complex code all over INSERT, UPDATE, and DELETE statements at the time of applying logic by combining these operations together. This statement generally works between two tables in which one is the source table and the other is the target table. It attempts to differentiate the source table from the target table which depends on the key field and then it begins processing. This statement is based on the actual indexes which have been utilized for comparing both source and target tables.

What is T-SQL merge?

The MERGE statement in T-SQL is a very famous clause that can control insert, update, and delete statements in one transaction in which there is no need to write separate logic for them because the merge can combine them together, it allows to clarify conditional functioning over the statements which we want to MERGE for inserting, deleting, and updating. The MERGE statement can provide the flexibility to the T-SQL in which we can able to customize our complex scripts so that we can easily able to read the complex scripts, the MERGE statement can easily modify the existing table which depends on the result of differentiating between key fields with the other table in the context.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

a

Let us see the illustration of the above figure, there are two circles that constitute tables so one is the source table and another is the target table in which merge is attempting to compare the source table with the target table depending on key fields, the merge statement can combine the insert, update, and delete operations together, we can say that the merge statement is complex than the simple insert or update, so once we are able to learn the concept then we can easily able to use the MERGE than usual insert or update.

  • T-SQL merge statement:

The merge statement in T-SQL is the merger of INSERT, UPDATE, and DELETE statements and if we have a source table and a target table then they are to be merged by using the MERGE statement,

  • we have two tables,

ITEM_LIST:

f

NEW_LIST:

l

  • Let us see how to get a new price for items in the ITEM_LIST, in which let us update the new cost in the ITEM_LIST from NEW_LIST as ITEM_LIST is the target table and NEW_LIST will be the source table.
  • As there are three mismatches between the above tables, as the cost of the first two products is different we have to update that as per the NEW_LIST in the ITEM_LIST and there is no Mariegold I_Name in the target but the source table has Monaco so we also have to update according to the NEW_LIST.
  • Hence, we have to perform,
  • UPDATE operation:

102   Bournvita     26

  • DELETE operation:

102   Mariegold   33

  • INSERT operation:
  • Monaco 33

So let us carry out the above operations by using the MERGE statement,

MERGE ITEM_LIST AS TARGET
USING NEW_LIST AS SOURCE
ON (TARGET.I_ID = SOURCE.I_ID)
WHEN MATCHED
AND TARGET.I_NAME <> SOURCE.I_NAME
OR TARGET.I_COST <> SOURCE.I_COST
THEN UPDATE
SET TARGET.I_NAME = SOURCE.I_NAME,
TARGET.I_COST = SOURCE.I_COST
WHEN NOT MATCHED BY TARGET
THEN INSERT (I_ID, I_NAME, I_PRICE)
VALUES (SOURCE.I_ID, SOURCE.I_NAME, SOURCE.I_COST)
WHEN NOT MATCHED BY SOURCE
THEN DELETE

Output,

T-SQL merge cc

In this way, we can able to carry out all three operations by using the MERGE statement.

T-SQL merge tables

The merge statement can perform processing on the different tables in which it can select the rows from the source table and as per the condition given it can insert, update, delete data into the target table,

Let us see the syntax below,

T-SQL merge ccc

Now let us understand the example,

MERGE INTO Subject TARGET
USING Student SOURCE
ON TARGET.SubId = SOURCE.SubId
WHEN MATCHED THEN
UPDATE TARGET.FirstName = SOURCE.FirstName, TARGET.LastName = SOURCE.LastName
WHEN NOT MATCHED THEN
INSERT into Subject (SubId, FirstName, LastName)
VALUES (SOURCE.SubId, SOURCE.FirstName, SOURCE.LastName);

In the above example we have used the merge statement, suppose we have two tables, College and Student and we have to reprint the data from the Student to the College table, as per the given conditions if the Subject is already available in the Subject table then it updates the FirstName and LastName if the Student not present in the Subject table then a new record has been added in the Subject table.

  • T-SQL merge OUTPUT:

T-SQL allows us to register conversion which is made by the MERGE statement with the help of the OUTPUT clause, hence if we want to outline all the operations in which the MERGE statement has been implemented for that it is needful to modify the surviving code to append the subsequent output activity, following code will provide the list of records on which the merge carry out, also operations which are carried out on every record,

MERGE TargetItems AS Target
USING Items AS Source
ON Source.ID = Target.ID
WHEN NOT MATCHED BY Target THEN
INSERT (ID, Item_Name, Cost)
VALUES (Source.ID, Source.Item_Name, Source.Cost)
WHEN MATCHED THEN UPDATE SET
Target.Item_Name = Source.Item_Name,
Target.Cost = Source.Cost
WHEN NOT MATCHED BY Source THEN DELETE
OUTPUT $action,
DELETED.ID AS Target_ID,
DELETED.Item_Name AS Target_Item_Name,
DELETED.Cost AS Target_Cost,
INSERTED.ID AS Source_ID,
INSERTED.Item_Name AS Source_Item_Name,
INSERTED.Cost AS SourceCost;

Output:

T-SQL merge ss

Example:

To utilize MERGE to perform UPDATE and DELETE operations on a table in a single statement:

Let us see an example of utilizing MERGE to update the ‘ItemInventory’ table in the AdventureWorks2013 sample database, which depends on the orders which are processed in the ‘SalesOrderDetail’ table, the ‘Total’ column of the ‘ItemInventory’ table has been updated by subtracting the number of orders which are placed for a day in ‘SalesOrderDetail’ table,

mm

Conclusion

In this article we conclude that the merge statements can combine insert, delete, and update operations together in a single statement, we have explained merge statements with various examples, we have also seen the merge statements, tables, and OUTPUT so this article will help to understand the concept of the merge statement.

Recommended Articles

This is a guide to T-SQL Merge. Here we discuss the introduction, overviews, What is T-SQL merge, examples with code implementation. You may also have a look at the following articles to learn more –

  1. PL/SQL varray
  2. SQL WAITFOR
  3. PL/SQL LIKE
  4. PL/SQL UNION
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more