T-SQL merge

Updated June 8, 2023

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.

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:

NEW_LIST:

• 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,

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,

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:

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,

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