0

I'm using SQL Server (SQL Server 2016 and Azure SQL) and I have this MERGE statement which uses a rather gnarly WHEN MATCHED condition to only update rows where values are actually different.

This is done for two reasons:

  1. The table has a rowversion column that will change when an UPDATE operation is performed, even if all of the values are the same. The rowversion values are useful for reducing client-activity (the application uses rowversion for optimistic concurrency).
  2. The table is also a Temporal Table, and SQL Server's implementation of Temporal Tables will add copies of live data to the History table whenever an UPDATE is performed, even if no values are actually changed.
CREATE PROCEDURE UpsertItems
    @groupId int,
    @items   dbo.ItemsList READONLY -- This is a table-valued parameter. The UDT Table-Type has the same design as the `dbo.Items` table.

WITH existing AS    -- Using a CTE as the MERGE target to allow *safe* use of `WHEN NOT MATCHED BY SOURCE THEN DELETE` and apparently it's good for performance.
(
    SELECT
        groupId,
        itemId,
        a,
        b,
        c,
        d,
        e,
        f,
        -- etc
    FROM
        dbo.Items
    WHERE
        groupId = @groupId
)
MERGE INTO existing WITH (HOLDLOCK) AS tgt
USING
    @items AS src ON tgt.itemId = src.itemId
WHEN MATCHED AND
(
    -- This part is painful, but unfortunately these are all NULLable columns so they need the full `x IS DISTINCT FROM y`-equivalent comparison:

    ( ( tgt.a <> src.a OR tgt.a IS NULL OR src.a IS NULL ) AND NOT ( tgt.a IS NULL AND src.a IS NULL ) )
    OR
    ( ( tgt.b <> src.b OR tgt.b IS NULL OR src.b IS NULL ) AND NOT ( tgt.b IS NULL AND src.b IS NULL ) )
    OR
    ( ( tgt.c <> src.c OR tgt.c IS NULL OR src.c IS NULL ) AND NOT ( tgt.c IS NULL AND src.c IS NULL ) )
    OR
    ( ( tgt.d <> src.d OR tgt.d IS NULL OR src.d IS NULL ) AND NOT ( tgt.d IS NULL AND src.d IS NULL ) )
    OR
    ( ( tgt.e <> src.e OR tgt.e IS NULL OR src.e IS NULL ) AND NOT ( tgt.e IS NULL AND src.e IS NULL ) )
    OR
    ( ( tgt.f <> src.f OR tgt.f IS NULL OR src.f IS NULL ) AND NOT ( tgt.f IS NULL AND src.f IS NULL ) )
    -- etc
)
THEN UPDATE SET
    tgt.a = src.a,
    tgt.b = src.b,
    tgt.c = src.c,
    tgt.d = src.d,
    tgt.e = src.e,
    tgt.f = src.f,
    -- etc
WHEN NOT MATCHED BY TARGET THEN INSERT (
    groupId,
    itemId,
    a,
    b,
    c,
    d,
    e,
    f,
    -- etc
)
VALUES (
    src.groupId,
    src.itemId,
    src.a,
    src.b,
    src.c,
    src.d,
    src.e,
    src.f,
    -- etc
)
WHEN NOT MATCHED BY SOURCE THEN DELETE

OUTPUT
    $action AS [Action],

    inserted.groupId AS Ins_groupId,
    deleted .groupId AS Del_groupId,
    inserted.itemId  AS Ins_itemId,
    deleted .itemId  AS Del_itemId,
    inserted.a       AS Ins_a,
    deleted .a       AS Del_a,
    inserted.b       AS Ins_b,
    deleted .b       AS Del_b,
    inserted.c       AS Ins_c,
    deleted .c       AS Del_c,
    inserted.d       AS Ins_d,
    deleted .d       AS Del_d,
    inserted.e       AS Ins_e,
    deleted .e       AS Del_e,
    inserted.f       AS Ins_f,
    deleted .f       AS Del_f,
    -- etc
;

As you can see, this is quit the pain to maintain!

I already use tools like T4 to automate generating the repetitive parts of this query, but the sheer... scale and pain of this MERGE statement makes me feel like I'm doing something very wrong (because software is meant to light-the-way via the Pit of Success, so if one is encountering difficulties trying to do the right thing you're probably doing it wrong), but I can't think or see a better way of accomplishing this (BULK INSERT notwithstanding, but for the purposes of this question that's not a possibility).

I know this statement can be simplified in other RDBMS which support x IS DISTINCT FROM y (which replaces the horrible-but-necessary NULL-safe checks in the WHEN MATCHED AND, but SQL Server still doesn't support it.

Another pain is the lack of DRY in SQL in general - and the difficulty of implementing a DRY database in SQL Server (e.g. there's no support for Deferred Constraints or Table Inheritance so you can't implement the Subclass Table Pattern, which means needless repetition of data-design in multiple tables and weaker constraints) - but that's another topic. I'm just frustrated with how backward SQL programming seems today compared to the many time-saving and keypress-saving features in modern languages like Kotlin and TypeScript.

Fantasy time:

I'd love to be able to do something like this, and not have to do with any gotchas (like how MERGE is unsafe by default without an explicit HOLDLOCK - that's crazy!):

MERGE INTO
    dbo.Items AS tgt
WHERE
    tgt.groupId = @groupId
FROM
    @items AS src
ON
    tgt.itemId = src.itemId
WHEN MATCHED AND DIFFERENT THEN UPDATE ( automap )
WHEN NOT MATCHED BY TARGET THEN INSERT ( automap )
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT ALL;

(Where automap would be an amazing feature in SQL Server that automatically maps source and destination columns by name and throws a compile-time error if it cannot automatically and safely map columns to each other), and OUTPUT ALL would output all $action, inserted, and deleted values with different column names - using the same column names but with inserted and deleted values in adjacent rows).

3 Answers 3

2

Use of EXCEPT is the best option, as noted by Brian, but this can be using directly in the MERGE as follows:

WHEN MATCHED AND EXISTS(
    SELECT src.*
    EXCEPT 
    SELECT tgt.*
    )

Here is a full working example:

DROP TABLE IF EXISTS #Test
CREATE TABLE #TEST (
    A INT PRIMARY KEY,
    B INT, 
    C INT,
)

INSERT INTO #Test 
    VALUES (1, 1, 1)
        ,(2, 1, 1)
        ,(3, 1, 1)
        ,(4, 1, NULL)
        ,(5, 1, NULL)
        ,(6, 1, NULL)

;WITH NewValues AS (
    SELECT * FROM (
        VALUES (2, 0, 1) --Update
            ,(3, 1, 1) -- Do Nothing
            ,(4, 0, NULL) -- UPDATE
            ,(5, 1, NULL) -- Do nothing, NULL = NULL
            ,(6, 1, 1) -- UPDATE 
            ,(7, 1, 1) -- INSERT
    ) V(A, B, C)
)
MERGE INTO #Test As t
USING 
    NewValues AS S ON t.A = s.A
WHEN MATCHED AND EXISTS(
    -- New values Exist Expcet when the match the old values exactly
    SELECT s.B, s.C 
    EXCEPT 
    SELECT t.B, t.C
    )
THEN UPDATE SET
    t.B = s.B,  t.C = s.C
WHEN NOT MATCHED BY TARGET THEN INSERT(
    A,  B,  C
) VALUES (s.A, s.B, s.C)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT
    $action AS [Action],
    inserted.*,
    deleted.*;

SELECT * FROM #TEST
4
  • Interesting! I'll be able to try this out with production data later in the week. I'll reply when I see how it does.
    – Dai
    Jun 10, 2020 at 6:47
  • Did not think of that. Wonder how it optimizes compared to the approach I have been using.
    – Brian
    Jun 10, 2020 at 19:59
  • I'd ask that you considering supporting this mssql change: feedback.azure.com/forums/908035-sql-server/suggestions/…, to minimize this type of pattern in general. Adding a comment to extend it to MERGE statements wouldn't be a bad idea.
    – Graham
    Jun 11, 2020 at 19:48
  • What version of SQL Server supports this? I'm using 2017 CompatibilityMode = 140.
    – jmoreno
    May 22 at 15:43
1

One option is to do deletes separately and get the data to be added or updated using an EXCEPT query.

SELECT a,b,c,d,e,f FROM SOURCE
EXCEPT
SELECT a,b,c,d,e,f FROM DESTINATION
1
  • Can you elaborate on this?
    – Dai
    Jun 9, 2020 at 23:36
-4

You can get something like "IS DISTINCT FROM" using CONCAT function.

WHEN MATCHED AND CONCAT(tgt.a, tgt.b, tgt.c, tgt.d, tgt.e, tgt.f) <> (src.a, src.b, src.c, src.d, src.e, src.f) THEN UPDATE....

3
  • 1
    This is a bad idea for too many reasons (e.g. it won't detect the difference between NULL and empty-strings, it will have poor performance at-scale, it may have issues with string values exceeding 4000 characters in length, it won't detect the difference between a = 1, b = 10 vs. a = 11, b = 0, etc)..
    – Dai
    Jun 6, 2020 at 4:07
  • there is something wrong if you have to compare more than 4000 characters here... :)
    – beto
    Jun 6, 2020 at 4:13
  • Hi and welcome to the forum! This might be better as a comment? You could flag it for a moderator to change it into one. Personally, I'm in favour of having a moderated queue for new users to be able to comment - I suggested this but was shot down :-(.
    – Vérace
    Jun 6, 2020 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.