SQL Server MERGE Statement: Question and Answer

Q.1. What is a Merge Statement in SQL Server 2012?

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.

Q.2. Show The Use of Merge Statement Using an Example?

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

Q.3. Define the Source and Target of MERGE?

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.

Q.4. How Does The Merge Statement Join Data Using The On Keyword?

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.

Q.5. StateThe Rules for Governing The Use of Multiple Merge Clauses?

Ans: In SQL Server, when using multiple merge clauses, certain rules need to be followed:

• Only one or two WHEN MATCHED clauses are allowed. If there are two WHEN MATCHED clauses, the first one must be qualified with an AND condition.
• One of the WHEN MATCHED clauses should specify a DELETE operation, and the other should specify an UPDATE operation. The MERGE statement will determine which one to execute based on the evaluation of the AND condition.
• Only one WHEN NOT MATCHED BY TARGET clause is allowed in a single MERGE statement.
• The WHEN NOT MATCHED BY TARGET clause can be qualified using an AND condition if needed, but specifying 'AND' without any condition is not meaningful.

Q.6. Describe With The Help of an Example The Use of The When Matched Clause?

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

Q.7. What is The When Not Matched By Target Clause?

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.

Q.8. How to Use Merge for Table Replication?

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.

Q.9. What is the When Not Matched By Source Clause? State The Rules to Follow While Using it. Explain Its Uses With The Help of an Example.

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').

Q.10. What is The Merge Output Clause?

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.

Q.11. Show With The Help of an Example The Use of The Merge Output Clause?

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.

Q.12. State and Explain The Different Types of Joins in Merge Statements?

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.

Q.13. What are The Valid Statements in The When Matched, When Not Matched By SOURCE, and When Not Matched By Target Clauses?

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.

Q.14. How Merge Incorporates The DML Behavior?

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.

Q.15. How Does SQL Server Internally Deploys The Merge Statement for The Stored Procedure That Manages Table Replication?

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.

Conclusion

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