How to debug merge in SQL Server?

sql-server

Solution

You can combine `MERGE` and the `OUTPUT` clause to get some kind of an "activity report" (or debug "print statements") of what the `MERGE` is doing - maybe that'll help you get an understanding for what's going wrong.

See Adam Machanic's excellent blog post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE that shows that technique and how to put it to use

Basically it boils down to using the `OUTPUT` clause with the `MERGE` statement to get the info on what's going on - something roughly along the lines of:

MERGE INTO ......
WHEN MATCHED THEN
    .......
WHEN NOT MATCHED THEN
    ......
WHEN NOT MATCHED BY SOURCE THEN
    ........
OUTPUT
    $action AS dml_action,
    inserted.x AS new_x,
    deleted.x AS old_x,
    inserted.y AS new_y,
    deleted.y AS old_y;

Problem

I am trying to learn how to use the MERGE operator. The following code compiles correctly: ``` ALTER PROCEDURE moto.procPM_UpdateLines @LineId As Int = null, @LineName As Varchar(100), @DeleteMe As Bit = 0 AS BEGIN MERGE moto.tblPMLine AS line USING (SELECT LineId, LineName FROM moto.tblPMLine) AS existsLine ON line.LineId = existsLine.LineId WHEN MATCHED AND @DeleteMe = 1 THEN DELETE WHEN MATCHED AND @DeleteMe = 0 THEN UPDATE SET line.LineName = @LineName WHEN NOT MATCHED THEN INSERT(LineName) VALUES(@LineName); END GO ``` It's a very simple procedure, I know, but for some reason it does not seem to generate any entries when I use the following command. ``` execute moto.procPM_UpdateLines null, 'First test', 0 ``` Is there a way for me to know which of the options it is following, if at all? Previous stored procedure fixed. ``` MERGE INTO moto.tblPMLine AS T USING (SELECT @LineId as LineId, @LineName as LineName) AS S ON T.LineId = S.LineId WHEN MATCHED AND @DeleteMe = 0 THEN --UPDATE UPDATE SET LineName = @LineName WHEN MATCHED AND @DeleteMe = 1 THEN --DELETE DELETE WHEN NOT MATCHED THEN--INSERT INSERT (LineName) VALUES (@LineName) OUTPUT $action AS ChangesMade; ``` Now it inserts, updates and deletes, as well as returning the output.

Original source