Labour Day Special : Flat $299 off on live classes + 2 free self-paced courses! - SCHEDULE CALL
Ans: The MERGE statement in SQL Server 2012 allows you to combine multiple operations like INSERT, UPDATE, and DELETE into a single statement. It is used to synchronize data between a source and target table efficiently. The MERGE statement uses a SELECT operation to provide the source and target data for the merge, and based on certain conditions, it performs the necessary insert, update, or delete actions on the target table. This condenses multiple statements into one, making it a powerful tool for managing data changes.
Ans: Let's illustrate the use of the MERGE statement with an example:
Consider two tables, 'Stock' and 'Trade', as follows:
Stock Table:
Symbol |
Qty |
ADVW |
10 |
BYA |
5 |
Trade Table:
Symbol |
Delta |
ADVW |
5 |
BYA |
-5 |
NWT |
3 |
We want to update the 'Stock' table with the changes recorded in the 'Trade' table. Let's perform the merge:
MERGE INTO Stock AS Target USING Trade AS Source ON Target.Symbol = Source.Symbol WHEN MATCHED AND (Target.Qty + Source.Delta = 0) THEN DELETE WHEN MATCHED THEN UPDATE SET Target.Qty = Target.Qty + Source.Delta WHEN NOT MATCHED BY TARGET THEN INSERT (Symbol, Qty) VALUES (Source.Symbol, Source.Delta); SELECT * FROM Stock;
After executing the MERGE statement, the 'Stock' table will be updated as follows:
Symbol |
Qty |
ADVW |
15 |
NWT |
3 |
Ans: In the MERGE statement, the source refers to the table or view from which the data is obtained for merging, and the target is the table that receives the merged data. The USING keyword specifies the source, and it can be any table, view, table-valued function, subquery, or CTE. The target is specified after the MERGE keyword, and it should be an updatable table or view. The source and target are joined based on the ON condition, and the matching rows are determined to perform the appropriate actions during the merge.
Ans: The MERGE statement uses the ON keyword to specify the join condition between the source and target tables. The ON condition indicates the columns that link the data in both tables for the merge. It functions similarly to a standard JOIN condition in a SELECT statement.
For example:
MERGE INTO TargetTable AS Target USING SourceTable AS Source ON Target.Column1 = Source.Column1;
The above statement performs a merge between 'TargetTable' and 'SourceTable' based on the equality of 'Column1' in both tables.
Ans: In SQL Server, when using multiple merge clauses, certain rules need to be followed:
Ans: The WHEN MATCHED clause in a MERGE statement is used when a match is found between the source and target based on the specified join condition. Let's illustrate this with an example:
Consider the 'Employees' table and the 'EmployeeUpdates' table as follows:
Employees Table:
EmpID |
FirstName |
LastName |
101 |
John |
Smith |
102 |
Jane |
Doe |
EmployeeUpdates Table:
EmpID |
FirstName |
101 |
Johnny |
103 |
Mike |
Now, let's use the MERGE statement to update the 'Employees' table with data from 'EmployeeUpdates':
MERGE INTO Employees AS Target USING EmployeeUpdates AS Source ON Target.EmpID = Source.EmpID WHEN MATCHED THEN UPDATE SET Target.FirstName = Source.FirstName WHEN NOT MATCHED BY TARGET THEN INSERT (EmpID, FirstName) VALUES (Source.EmpID, Source.FirstName); SELECT * FROM Employees;
After executing the MERGE statement, the 'Employees' table will be updated as follows:
EmpID |
FirstName |
LastName |
101 |
Johnny |
Smith |
102 |
Jane |
Doe |
103 |
Mike |
NULL |
Ans: The WHEN NOT MATCHED BY TARGET clause in a MERGE statement is used to handle rows that are present in the source but not in the target. It identifies data in the source that needs to be inserted into the target table. This clause allows you to perform an INSERT operation for the missing rows. For example:
MERGE INTO TargetTable AS Target USING SourceTable AS Source ON Target.KeyColumn = Source.KeyColumn WHEN NOT MATCHED BY TARGET THEN INSERT (Column1, Column2, ...) VALUES (Source.Column1, Source.Column2, ...);
In the above statement, the WHEN NOT MATCHED BY TARGET clause will insert rows from the 'SourceTable' into the 'TargetTable' where the 'KeyColumn' values do not exist in the target.
Ans:Table replication using MERGE can be achieved by creating two tables with identical schemas, one as the original and the other as the replica. Then, you can use the MERGE statement in a stored procedure to synchronize data between the two tables.
Here's an example:
CREATE TABLE Original ( PK INT PRIMARY KEY, FName VARCHAR(50), Number INT ); CREATE TABLE Replica ( PK INT PRIMARY KEY, FName VARCHAR(50), Number INT );
CREATE PROCEDURE SyncReplica AS BEGIN MERGE INTO Replica AS R USING Original AS O ON R.PK = O.PK WHEN MATCHED AND (R.FName != O.FName OR R.Number != O.Number) THEN UPDATE SET R.FName = O.FName, R.Number = O.Number WHEN NOT MATCHED THEN INSERT VALUES (O.PK, O.FName, O.Number) WHEN NOT MATCHED BY SOURCE THEN DELETE; END;
In this example, the stored procedure 'SyncReplica' uses the MERGE statement to synchronize data between the 'Original' and 'Replica' tables based on the primary key (PK) column. It handles inserts, updates, and deletes efficiently in one operation.
Ans: The MERGE OUTPUT clause allows you to retrieve the data affected by the MERGE statement. It enables you to see the actual changes made during the merge operation, such as the rows inserted, updated, or deleted. The OUTPUT clause returns the inserted and deleted pseudo-tables, as well as a virtual column called '$action' that indicates the type of operation performed for each row ('INSERT', 'UPDATE', or 'DELETE').
Ans: Let's modify the previous example of table replication to include the MERGE OUTPUT clause:
CREATE PROCEDURE SyncReplica AS BEGIN MERGE INTO Replica AS R USING Original AS O ON R.PK = O.PK WHEN MATCHED AND (R.FName != O.FName OR R.Number != O.Number) THEN UPDATE SET R.FName = O.FName, R.Number = O.Number WHEN NOT MATCHED THEN INSERT (PK, FName, Number) VALUES (O.PK, O.FName, O.Number) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, inserted.*, deleted.*; END;
When you call the 'SyncReplica' stored procedure, it will perform the merge and also return the results using the MERGE OUTPUT clause:
EXEC SyncReplica;
The OUTPUT will provide a detailed report of the changes made during the merge operation, showing the action ('INSERT', 'UPDATE', or 'DELETE'), as well as the inserted and deleted data for each row.
Ans: When employing the OUTPUT clause in conjunction with the MERGE statement, an additional virtual column known as "$action" is introduced alongside the columns from the INSERTED and DELETED pseudo tables. Depending on the action taken for each record, the "$action" column will yield one of three string values: "INSERT," "UPDATE," or "DELETE." The uspSyncReplica stored procedure is slightly modified, incorporating an OUTPUT clause within the MERGE statement. Through the OUTPUT clause, the virtual "$action" column and every column from the INSERTED and DELETED pseudo tables are selected, allowing the MERGE statement to furnish a comprehensive report of each executed DML transaction.
CREATE PROCEDURE uspSyncReplica AS MERGE Replica AS R USING Original AS O ON O.PK R.PK WHEN MATCHED AND (0. FName != R. FName OR 0.Number != R. Number) THEN 0. FName, R.Number = 0.Number UPDATE SET R. FName WHEN NOT MATCHED THEN INSERT VALUES (O.PK, 0. FName, O.Number) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, INSERTED.*, DELETED.*;
Listing: Using the OUTPUT condition and $action virtual column together with MERGE
If you have followed the previous version of instructions, it is imperative to first drop all elements—tables and the stored procedure.The resulting outcome from implementing this modified MERGE command for the final set of modifications within the replication example is as follows:
$action PK FName Number PK FName Number DELETE NULL NULL NULL 1 Sara 10 UPDATE 2 Stephen 30 2 Steven 20 INSERT 3 Andrew 100 NULL NULL NULL (3 row(s) affected)
This output illustrates all actions performed by the MERGE statement, coupled with pertinent data before and after the operations. Naturally, you have the option to direct this data to other tables for historical tracking or additional processing using the OUTPUT...INTO syntax, akin to its use in INSERT, UPDATE, and DELETE statements. As we conclude our discussion on MERGE, you'll delve further into the INSERT OVER DML syntax introduced in SQL Server 2008, offering enhanced versatility.
Ans: SQL Server adeptly selects the most suitable type of join from four different options, based on the merge clauses found within the MERGE command.
Table: Join Types Selected by SQL Server Depending on Your Merge Clause(s) for Source-to-Target Table
The WHEN MATCHED clause retrieves rows discovered in both tables and matching values in the joining key columns set with ON. This operation is akin to an inner join, as it fetches records present on both sides. Within the WHEN MATCHED clause, you have the flexibility to perform either an UPDATE or a DELETE operation on matched target rows. The WHEN NOT MATCHED [BY TARGET] clause, which exclusively gathers source rows absent in the target, operates as a left outer join. It addresses the task of filling gaps in the target with missing source data, making only an INSERT operation viable within this clause.
When coupled with the WHEN NOT MATCHED [BY TARGET] clause, the WHEN NOT MATCHED BY SOURCE clause introduces a right outer join, exclusively returning target rows missing in the source. Like the WHEN MATCHED clause, this scenario also allows for executing an UPDATE or DELETE operation on target rows absent from the source.Lastly, when WHEN NOT MATCHED [BY TARGET] is combined with either or both of the other two clauses, it encompasses both target data found or not found in the source, along with source data not located in the target. In this context, a comprehensive join occurs between the two tables. The WHEN NOT MATCHED [BY TARGET] clause introduces source data absent in the target, while the other clause(s) facilitate updating or deleting target data, respectively.
Ans: The sole permissible logical declarations within the WHEN MATCHED or WHEN NOT MATCHED BY SOURCE clauses are UPDATE or DELETE. Furthermore, the only valid statement permitted within the WHEN NOT MATCHED [BY TARGET] clause is INSERT. None of these merge clauses can accommodate additional T-SQL statements, including invocations of stored procedures. Nevertheless, user-defined functions may be invoked in the INSERT or UPDATE statements within a merge clause.
Ans: The merging process comprises four distinct DML statements: SELECT, INSERT, UPDATE, and DELETE. The MERGE command integrates these statements, executing them in a unified manner. The unique DML statements within MERGE are internally executed consistently. When tables or updatable views are designated as the target within a MERGE statement, any existing AFTER and INSTEAD OF triggers for these tables or views continue to be triggered. For example, when the WHEN NOT MATCHED THEN...INSERT clause is executed, insert triggers configured for the target are activated, and when the WHEN MATCHED THEN DELETE clause is executed, delete triggers are triggered.
You are able to seamlessly incorporate MERGE into your T-SQL code, as there is no concept of a "merge trigger" in this context. Triggers function just as they would if separate statements were used instead of MERGE. When utilizing MERGE, you have access to the same features and options available with distinct DML statements. For instance, you can utilize the WITH clause preceding the MERGE statement and reference it using the USING clause, enabling the use of a Common Table [removed]CTE) as the source of the merge operation. Both the TOP clause and traditional query hints are equally supported.
Ans: The query execution plan of the database engine allows you to observe how SQL Server internally executes the MERGE statement within the stored procedure responsible for table replication.
Image: Query plan for a MERGE clause to match a replica with an original
The initial step in this task involves scanning the source and target tables (query plans are read from left to right). Notably, creating indexes on the columns that link the Original and Replica tables for effective merging becomes evident. To optimize read performance, the query optimizer employs a clustered index scan, especially when the join attribute utilizes the primary keys of both tables. Therefore, it is recommended to establish an index (preferably unique, and ideally clustered) on the columns involved in joining the two tables.
The plan further illustrates the execution of a full outer join between the two tables. The type of join employed is dictated by the presence or absence of the WHEN...THEN merge clauses within the MERGE statement, as mentioned earlier. Given that the MERGE command utilizes all three possible merge clauses, in this scenario, SQL Server executes a full outer join.
Subsequent to the initial merge clause, the procedure involving the clustered index merge implements the condition that selects matched rows only if any non-key columns have been altered between the original and replica tables. The clustered index merge process utilizes the output from the full outer join and assesses, on a row-by-row basis, the syntax of the MERGE statement and the data in the source and target tables. Based on this information, it determines whether a row, upon reaching this stage, should undergo processing as an update, an insert, or a delete.
In conclusion, our exploration of the MERGE statement has provided insights into its purpose and applications. As you continue to delve into the realm of SQL operations, considering engaging in online SQL training can greatly enhance your understanding and proficiency. Such training will offer you the opportunity to delve deeper into the intricacies of the MERGE statement, helping you master its usage and address any queries that may arise as you work with it
Mastering INSERT and OVER DML Syntax: Interview Questions Guide
SQL CLR Deployment and Error Resolution: Question and Answer
Mastering PowerPivot: Question and Answer
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment