CODEWITHSUNDEEP
sql-servermerge
zu1b
zu1b

Reputation: 585

How can prevent a SQL Server merge using OUTPUT from updating the target table

I know how to update a target table from a source table using a SQL server MERGE statement. I know I can capture the changes using an OUTPUT statement.

Problem: When using output statement to capture the changes they are still applied to the target table.

Question: How can I prevent the MERGE statement from updating the target and only capture the output?

Extra info: The goal in this case is not to audit, which is the common OUTPUT statement use in MERGE. The goal is to determine the changes and then do further processing on them that cannot be added to the merge. You could also call it doing a Dry-Run.

Upvotes: 1

Views: 506

Answers (1)

John Hurrell
John Hurrell

Reputation: 350

In order for MERGE to emit anything as part of the OUTPUT clause, it would have to perform an UPDATE or INSERT.

Off the top of my head, I can see two means to accomplish what you want:

One: Write some SQL to capture the data set that feeds into the MERGE statement and use it to figure out what the MERGE would do. I know I didn't explain that very well so here's an example with pseudo-code.

SELECT
    s.*
    ,d.PrimaryKey
INTO
    #SourceForMerge
FROM
    [Source] AS s
    LEFT OUTER JOIN [Destination] AS d
        ON s.MatchingColumnA = d.MatchingColumnA
        AND s.MatchingColumnB = d.MatchingColumnB

;MERGE [Destination] AS d
USING
(
    SELECT * FROM #SourceForMerge
) AS s
ON
    d.PrimaryKey = s.PrimaryKey
WHEN MATCHED THEN
    -- UPDATE columns
WHEN NOT MATCHED THEN
    -- INSERT columns
OUTPUT
    -- columns
INTO
    #OutputTempTable

This would allow you to interrogate the #SourceForMerge temp table to see what matched and what didn't.

Alternatively, you could be an outlaw and wrap the MERGE in a transaction, capture the OUTPUT, SELECT it somewhere and then deliberately ROLLBACK to "undo" the changes.

This strikes me as the most accurate method but also a little scary.

When I'm testing some SQL, I'll often do this by wrapping something in a TRANSACTION and then having something like:

SELECT 1 / 0

to trigger a ROLLBACK.

Upvotes: 1

Related Questions