Unlock T-SQL Merge Statements with this Worked Example

There are many scenarios where you need to update, delete or insert new records in a table. The T-SQL Merge statement combines these operations into one statement. In simple terms, the T-SQL Merge statement updates one table (the target) based on values in another table (the source).

This post is not designed to teach you all about Transact SQL. There are plenty of great resources for that (and yes... you could spend the rest of your life learning). This post simply provides a worked example using a basic scenario and also discusses a few more advanced use cases.

T-SQL Merge - Business Unit - Worked Example

This example is intentionally simple with a very small data set. Hopefully, that helps with visualizing what's actually going on. Note: I've scripted the whole example and that code is pasted below.

Let's say you have a table that describes your businesses Business Units and their Managers. It looks like:

But after a reorganization, the Business Unit structure and Managers have changed. Let's say the desired change looks like this:

From a T-SQL Merge statement perspective, the first table is the 'TARGET' table. This is the target of the merge or the table we want to change.

The second table is the 'SOURCE' table. In this simple example, I initially started using an actual table, although often this is generated from a CTE (common table expression) or similar. I then realized that ID 4 was repeated, so I removed it from the source table. But I still wanted to ensure that target retained this specific record. There are a number of ways I could have achieved this and to keep things simple I introduced a basic UNION query when generating the source. Here it is:

USING
(
SELECT * FROM dbo.BU_New
UNION
SELECT
BU_NewKey = BusinessUnitKey
, BusinessUnitName
, Manager
FROM dbo.BusinessUnits
WHERE BusinessUnitKey = 4
)
AS source

This clearly demonstrates that a query can be used to build up the Source.

Anyway... When comparing the TARGET to the SOURCE there are a number of things that should be apparent:

Several new records (Like Marketing) are needed.
Several existing records need updating. Like Accounting being changed to Finance.
The 'External' record needs to be deleted.
The 'Projects' record (ID 4 from the UNION) needs to be retained with no change.

All of these things are achieved with a single TSQL Merge Statement.

The resulting TARGET table looks like this:

Example T-SQL Merge Code

Below is the example code in the form of a stored procedure. It creates the source and target tables and then runs the T-SQL Merge statement.

CREATE PROCEDURE [dbo].[Uspdatainsightmergeexample]
AS
  BEGIN
      IF Object_id (N'dbo.BusinessUnits', N'U') IS NOT NULL
        DROP TABLE dbo.businessunits;

      CREATE TABLE dbo.businessunits
        (
           businessUnitKey  TINYINT NOT NULL PRIMARY KEY,
           businessUnitName NVARCHAR(30),
           manager          NVARCHAR(50)
        );

      INSERT INTO dbo.businessunits
      VALUES      (1,
                   'Human Resources',
                   ''),
                  (2,
                   'Sales',
                   'Smith'),
                  (3,
                   'Accoounting',
                   'Chan'),
                  (4,
                   'Projects',
                   'Meredith'),
                  (11,
                   'External',
                   'Test');

      IF Object_id (N'dbo.BU_New', N'U') IS NOT NULL
        DROP TABLE dbo.bu_new;

      CREATE TABLE dbo.bu_new
        (
           bu_newkey        TINYINT NOT NULL PRIMARY KEY,
           businessUnitName NVARCHAR(30),
           manager          NVARCHAR(50)
        );

      INSERT INTO dbo.bu_new
      VALUES      (1,
                   'Human Resources',
                   'Malory'),
                  (2,
                   'Sales',
                   'McBrien'),
                  (3,
                   'Finance',
                   'Chan'),
                  (5,
                   'Marketing',
                   'Phillips'),
                  (6,
                   'Production',
                   'Jones'),
                  (7,
                   'Analytics',
                   'Bryden');

      MERGE dbo.businessunits WITH (holdlock) AS target
      using (
       SELECT * FROM dbo.BU_New
       UNION
       SELECT
         BU_NewKey = BusinessUnitKey
         , BusinessUnitName
         , Manager
       FROM dbo.BusinessUnits
       WHERE BusinessUnitKey = 4
      ) AS source
      ON ( target.businessunitkey = source.bu_newkey )
        WHEN MATCHED AND EXISTS (
        SELECT target.manager, target.businessunitname
        EXCEPT
        SELECT source.manager, source.businessunitname
      )
        THEN UPDATE SET
         target.manager = source.manager,
         target.businessUnitName = source.businessUnitName
      WHEN NOT MATCHED THEN
        INSERT ( businessUnitKey,
                 businessUnitName,
                 manager )
        VALUES ( source.bu_newkey,
                 source.businessunitname,
                 source.manager )
      WHEN NOT MATCHED BY source THEN
        DELETE
      output $action,
             inserted.businessunitkey AS sourcebu_newkey,
             inserted.businessunitname AS sourcebuname,
             inserted.manager AS sourcemanager,
             deleted.businessunitkey AS targetbusinessunitkey,
             deleted.businessunitname AS targetbuname,
             deleted.manager AS targetmanager;
  END