Introduction to Merge SQL
Whenever there are two tables or multiple statements that are related and act as source and the target table such that any changes in the source table make the way for changing the contents of the target table. To do the changes on the target table whenever any changes in the source table are made, we can make the use of a merge statement in SQL which helps us to perform the insert, update and delete statements togetherly as a part of single query execution.
We can execute all the three operations involving the deletion, insertion, and updation of the target tables whenever the source statements are modified by using the merge statement at once. In this article, we will learn about the syntax of the merge statement and also learn about its implementation with the help of multiple examples.
Syntax of Merge SQL
The syntax of the MERGE statement in SQL is as shown below:
MERGE <target table> [AS TARGET]
USING <source table> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge the matched records> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <perform merge operations when matching record is not found in the target table> ]
[WHEN NOT MATCHED BY SOURCE
THEN <perform merge operations when matching record is not found in the source table> ];
In the above statement, we can use the merge statement to execute any of the updates, insert and delete operations togetherly based on the records that are matched or unmatched from the target and source tables that are compared by specifying conditional like join on the column contents of both the tables and according to decide which operation should be performed on what condition for all the possible matched and unmatched all using one single statement of the merge.
In the above syntax, the target table is the name of the table on which the operations are to be performed on the occurrence of results of comparison of conditions. The source table is the name of the table whose changes will act as an event to cause changes in the target table by executing a merge statement. The search condition can be multiple conditions separated by the logical operators such as AND and OR similar to the condition specification in on clause of the joint statement. Further on the result of the conditions that are specified the flow will then be transferred either to the matched case, not matched case by target or source, and perform the desired operations.
Examples of Merge SQL
Let us understand the implementation of the merge statement with the help of an example. Whenever using the merge statement it is necessary to identify the source table, target table, and the operations that need to be performed on the target table whenever the changes are being made in the source statement. Consider the two existing tables named Articles and Updated Articles whose contents and structure are as shown in the output of the following query statement:
SELECT * FROM Articles;
The execution of the above query statement gives an output which is as shown below –
SELECT * FROM UpdatedArticles;
The execution of the above query statement gives an output which is as shown below –
Now, what we have to do that the contents that are present in the updated articles table should be considered as a source table and whenever any changes are made in the source table we have to perform operations on the target table. Here, the target table is the articles table and we have to synchronize the data between articles and updated articles table.
The merge statement can be used by considering articles as the target table and updated articles as the source table. We can make the join and match based on the unique primary key columns of both tables named article id and decide that the record is matched or not by matching the contents of this column of both tables. The actions that need to be performed on comparison of the records are as mentioned below –
- Whenever a matching id is found in the target table for source table record and the contents of article topic and rate are found to be mismatched and not equal then we will have to perform the update operation and update the contents of the target table articles according to the contents of that record in the source table.
- When a matching entry is not found in the source table then the delete operation is to be performed on the target table articles.
- When a matching entry is not found in the target table then the insert operation is to be performed on the target table articles by copying the contents of that id from the source table.
Hence, according to the requirements as mentioned above for performing multiple operations on the target table using a single merge statement, we can write the merge statement as shown below which will satisfy all the conditions and perform necessary operations.
MERGE Articles AS TARGET
USING UpdatedArticles AS SOURCE
ON (TARGET.ArticleID = SOURCE.ArticleID)
WHEN MATCHED AND TARGET.ArticleTopic <> SOURCE.ArticleTopic OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ArticleTopic = SOURCE.ArticleTopic, TARGET.Rate = SOURCE.Rate
WHEN NOT MATCHED BY TARGET
THEN INSERT (ArticleID, ArticleTopic, Rate) VALUES (SOURCE.ArticleID, SOURCE.ArticleTopic, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $ACTION,
DELETED.ArticleID AS TargetArticleID,
DELETED.ArticleTopic AS TargetArticleTopic,
DELETED.Rate AS TargetRate,
INSERTED.ArticleID AS SourceArticleID,
INSERTED.ArticleTopic AS SourceArticleTopic,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
The execution of the above query statement gives an output which is as shown below –
We can observe that the records with id 2 and 3 existed in both the tables but varied in the rate hence rate of the source table was updated in the target table by executing an update query on the target table. For id with 4 there was no record in the updated articles source table hence it was deleted from the target table and for id 5 a new record was inserted in the target table updated articles as there was no such entry in it.
Conclusion
We can club multiple operations in a single merge statement when two tables are present such that changes in one table i.e. source table should make the equivalent or corresponding in another table that is the target table.
Recommended Articles
This is a guide to Merge SQL. Here we also discuss the introduction and syntax of merge sql along with a different example and its code implementation. You may also have a look at the following articles to learn more –
4.5 (9,032 ratings)
View Course
