T-SQL: Avoid MERGE. Write separate statements instead


The MERGE statement was introduced in SQL Server 2008 and the developers embraced it immediately. Later a feedback came and the statement was criticized as not very efficient.

Aaron Bertrand collected some links about this here.

MERGE statement anatomy

The MERGE statement joins ‘source’ and ‘target’ tables and runs INSERT, UPDATE or DELETE statement on the ‘target’ table.

The anatomy:

So let’s split it into separate statements.

Prepare the playground:

use PL;

drop table if exists ##source_table_name;
create table ##source_table_name
  (
    column_name_1 int
    , column_name_2 int
    , column_name_3 nvarchar(128)
    , column_name_4 nvarchar(128)
    , column_name_5 nvarchar(128)
  );

drop table if exists ##target_table_name;
create table ##target_table_name
  (
    column_name_1 int
    , column_name_2 int
    , column_name_3 nvarchar(128)
    , column_name_4 nvarchar(128)
    , column_name_5 nvarchar(128)
  );

insert ##source_table_name (column_name_1, column_name_2, column_name_3, column_name_4, column_name_5)
select 1, 2, 'A', 'B', 'C'
union all select 999, 1000, 'X', 'Y', 'Z';

Another tab in SSMS:

select * from ##source_table_name;
select * from ##target_table_name;

WHEN NOT MATCH (BY TARGET is optional) THEN INSERT

On pages 132 and 133 of Learn SQL Server Intuitively, i explain how to combine the LEFT or RIGHT JOIN with a WHERE clause.

Single query to INSERT:

INSERT ##target_table_name (column_name_1, column_name_2, column_name_3, column_name_4, column_name_5)
SELECT s.column_name_1, s.column_name_2, s.column_name_3, s.column_name_4, s.column_name_5
FROM
  ##source_table_name AS s
  LEFT JOIN ##target_table_name AS t
    ON s.column_name_1 = t.column_name_1
    AND s.column_name_2 = t.column_name_2
WHERE
  t.column_name_1 IS NULL
  AND t.column_name_2 IS NULL;

WHEN MATCHED (and at least one column is different) THEN UPDATE

Make a change in the source:

UPDATE ##source_table_name
SET column_name_4 = 'W'
WHERE column_name_1 = 1;

Single query to UPDATE:

UPDATE ##target_table_name
  SET
    column_name_3 = s.column_name_3
    , column_name_4 = s.column_name_4
    , column_name_5 = s.column_name_5
FROM
  ##source_table_name AS s
  JOIN ##target_table_name AS t
    ON s.column_name_1 = t.column_name_1
    AND s.column_name_2 = t.column_name_2
    AND 
    (
      s.column_name_3 != t.column_name_3
      OR s.column_name_4 != t.column_name_4
      OR s.column_name_5 != t.column_name_5
    );

WHEN NOT MATCH BY SOURCE THEN DELETE

The same logic as the INSERT statement, just RIGHT JOIN instead.

Delete from source:

DELETE ##source_table_name
WHERE column_name_1 = 999;

Single query to DELETE:

DELETE t
FROM
  ##source_table_name AS s
  RIGHT JOIN ##target_table_name AS t
    ON s.column_name_1 = t.column_name_1
    AND s.column_name_2 = t.column_name_2
WHERE
  s.column_name_1 IS NULL
  AND s.column_name_2 IS NULL;

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *