CODEWITHSUNDEEP
t-sqlmergesql-server-2014
Menno
Menno

Reputation: 12621

MERGE - conditional "WHEN MATCHED" OUTPUT

The following code shows two WHEN MATCHED-clauses. The first one matches when only the DATETIME-column 'updatedAt' is changed. This should update the target, however I don't want this marked in the OUTPUT. The second one matches when there are other changes in the given match, this should lead to an OUTPUT.

MERGE [Target].dbo.[clients] AS target
USING [Source].dbo.[clients] AS source
ON target.[objectId]=source.[objectId]
WHEN MATCHED AND NOT EXISTS (
                    SELECT source.firstName, ...
                    EXCEPT
                    SELECT target.firstName, ...
                ) AND source.updatedAt <> target.updatedAt
THEN 
    UPDATE SET --THIS UPDATE SHOULD NOT LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.updatedAt = source.updatedAt
WHEN MATCHED AND EXISTS (
                    SELECT source.firstName, ... , source.updatedAt
                    EXCEPT
                    SELECT target.firstName, ... , target.updatedAt
                )
THEN
    UPDATE SET --THIS UPDATE SHOULD LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.[firstName]=source.[firstName], ...
WHEN NOT MATCHED BY TARGET
THEN
    INSERT ([objectId],[firstName], ... ,[updatedAt]) VALUES ([objectId],[firstName], ... ,[updatedAt])
WHEN NOT MATCHED BY SOURCE
THEN
    DELETE

OUTPUT 
    $ACTION ChangeType
    , ISNULL(
        inserted.objectId
        , deleted.objectId
    ) AS objectId
    , GETDATE() AS DateTimeChanged
;

This is achievable by outputting all source and target columns and putting the MERGE in a subquery, to make the comparison in a main query, e.g.:

WHERE NOT (
    [ChangeType]='UPDATE' 
    AND [src objectId]=[tgt objectId] 
    AND [src firstName]=[tgt firstName] 
    AND ... 
    AND [src updatedAt]<>[tgt updatedAt]
)

However, I feel there should be a better way, since I am able to declare two different WHEN MATCHED-clauses. Is there any better way?

Upvotes: 3

Views: 4348

Answers (1)

Zikato
Zikato

Reputation: 605

I don't think your example is correct, because you have two conditional WHEN MATCHED clauses.

as per Books online: Merge (emphasis mine)

WHEN MATCHED THEN < merge_matched >
Specifies that all rows of *target_table, which match the rows returned by ON , and satisfy any additional search condition, are either updated or deleted according to the clause.

The MERGE statement can have, at most, two WHEN MATCHED clauses. If two clauses are specified, the first clause must be accompanied by an AND clause. For any given row, the second WHEN MATCHED clause is only applied if the first isn't. If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. When UPDATE is specified in the clause, and more than one row of matches a row in target_table based on , SQL Server returns an error. The MERGE statement can't update the same row more than once, or update and delete the same row.

I've also tried your code 

BEGIN TRANSACTION
SET XACT_ABORT ON;

CREATE TABLE TargetClients
(
    objectId BIGINT
    , firstName VARCHAR(50)
    , updatedAt DATETIME2(0)

)

CREATE TABLE SourceClients
(
    objectId BIGINT
    , firstName VARCHAR(50)
    , updatedAt DATETIME2(0)

)

go

MERGE TargetClients AS target
USING SourceClients AS source
ON target.[objectId]=source.[objectId]
WHEN MATCHED AND NOT EXISTS (
                    SELECT source.firstName
                    EXCEPT
                    SELECT target.firstName
                ) AND source.updatedAt <> target.updatedAt
THEN 
    UPDATE SET --THIS UPDATE SHOULD NOT LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.updatedAt = source.updatedAt
WHEN MATCHED AND EXISTS (
                    SELECT source.firstName, source.updatedAt
                    EXCEPT
                    SELECT target.firstName, target.updatedAt
                )
THEN
    UPDATE SET --THIS UPDATE SHOULD LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.[firstName]=source.[firstName]
WHEN NOT MATCHED BY TARGET
THEN
    INSERT ([objectId],[firstName], [updatedAt]) VALUES ([objectId],[firstName], [updatedAt])
WHEN NOT MATCHED BY SOURCE
THEN
    DELETE

OUTPUT 
    $ACTION ChangeType
    , ISNULL(
        inserted.objectId
        , deleted.objectId
    ) AS objectId
    , GETDATE() AS DateTimeChanged
;



rollback

And it showed me the error

Msg 10714, Level 15, State 1, Line 33 An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

One possible solution for you would be to use CASE statement inside the Merge_matched clause and use an extra column as a flag.

Upvotes: 2

Related Questions