MERGE Statement Example

Hello, I’m not a huge fan of the MERGE statement, as I find the syntax a little clunky. Plus, there are some drawbacks to it that I mentioned in a previous post here. A quick internet search for “SQL Merge statement problems” will yield several articles about the issues with MERGE statements.

However, one benefit of the MERGE statement is that it is an atomic transaction. The entire operation of checking and performing either an INSERT, UPDATE, or DELETE is atomic, meaning it is a single unit of work that either fully completes or does not execute at all. This is beneficial in concurrent environments where multiple users might modify the data simultaneously.

In this blog post, I generate a set of straightforward test data and furnish a SQL example that efficiently illustrates the varied clauses within the MERGE statement.

First, I think this might be somewhat obvious, hopefully, but only the target table can be operated on in the merge statement. The source table can only be used as a comparison set.

Here are the following clauses in the MERGE statement and what operations are possible.

WHEN MATCHED: Permits operations UPDATE or DELETE.
WHEN NOT MATCHED BY TARGET: Allows the INSERT operation.
WHEN NOT MATCHED BY SOURCE: Permits operations UPDATE or DELETE.

Looking at the following diagram, the following values represent the various matches

WHEN MATCHED = 1,2
WHEN NOT MATCHED BY TARGET = 3
WHEN NOT MATCHED BY SOURCE = 4

Ultimately, I’ve discovered that the most effective way to learn MERGE statements is through this code. Feel empowered to duplicate and experiment with it on your own. For easier understanding, I’ve named my tables as #SourceTable and #TargetTable. The employee’s last names explicitly include “Target” and “Source” for clarity. The columns I’ve created include IsMatched, IsNotMatchedByTarget, IsNotMatchedBySource, and additionally, I demonstrate how you can incorporate predicate logic into various clauses.

SET NOCOUNT ON
GO

DROP TABLE IF EXISTS #SourceTable;
DROP TABLE IF EXISTS #TargetTable;
GO

-- Source Table
CREATE TABLE #SourceTable (
    ID INT NOT NULL,
    TableType VARCHAR(10) NOT NULL,
    EmployeeName NVARCHAR(50) NOT NULL,
    Department VARCHAR(10) NOT NULL,
    ModifiedDate DATETIME DEFAULT GETDATE()
);
GO

-- Target Table
CREATE TABLE #TargetTable (
    ID INT NOT NULL,
    TableType VARCHAR(10) NULL,
    EmployeeName VARCHAR(50) NOT NULL,
    Department VARCHAR(10) NOT NULL,
    IsMatched SMALLINT DEFAULT 0,
    IsNotMatchedByTarget SMALLINT DEFAULT 0,
    IsNotMatchedBySource SMALLINT DEFAULT 0,
    ModifiedDate DATETIME DEFAULT GETDATE()
);
GO

-- INSERT statements for TargetTable
INSERT INTO #TargetTable (ID, TableType, EmployeeName, Department) VALUES (1, 'Target', 'Tommy Target', 'Accounting');
INSERT INTO #TargetTable (ID, TableType, EmployeeName, Department) VALUES (2, 'Target', 'Toni Target', 'Marketing');
INSERT INTO #TargetTable (ID, TableType, EmployeeName, Department) VALUES (4, 'Target', 'Trisha Target', 'IT');

-- INSERT statements for SourceTable
INSERT INTO #SourceTable (ID, TableType, EmployeeName, Department) VALUES (1, 'Source', 'Sally Source', 'Accounting');
INSERT INTO #SourceTable (ID, TableType, EmployeeName, Department) VALUES (2, 'Source', 'Sheila Source', 'Marketing');
INSERT INTO #SourceTable (ID, TableType, EmployeeName, Department) VALUES (3, 'Source', 'Sammy Source', 'Finance');
GO

--------------------------------------------------------------------

MERGE #TargetTable AS trgt
USING #SourceTable AS src
ON trgt.ID = src.ID AND trgt.ID > 0 AND src.ID > 0

WHEN MATCHED THEN
    UPDATE SET trgt.IsMatched = 1

WHEN NOT MATCHED BY TARGET AND src.ID > 0 THEN
    INSERT (ID, EmployeeName, Department, IsNotMatchedByTarget)
    VALUES (src.ID, src.EmployeeName, src.Department, 1)

WHEN NOT MATCHED BY SOURCE AND trgt.ID > 0 THEN
    UPDATE SET trgt.IsNotMatchedBySource = 1;

GO

SELECT * FROM #TargetTable;
GO

In summary, the SQL MERGE statement, despite its initial appearance of complexity, can be harnessed effectively with a comprehensive understanding of its clauses and conditions. However, given its potential to perform extensive modifications, it should be wielded carefully. Happy exploring, and may your queries return the desired results.