CODEWITHSUNDEEP
sql-server
Jesus Zamora
Jesus Zamora

Reputation: 839

How to debug merge in SQL Server?

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.

Upvotes: 9

Views: 11312

Answers (2)

marc_s
marc_s

Reputation: 754538

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;

Upvotes: 16

shahkalpesh
shahkalpesh

Reputation: 33474

MERGE moto.tblPMLine AS line
USING (SELECT LineId, LineName FROM moto.tblPMLine WHERE LineID = @LineID) AS existsLine

Pardon me as I haven't used MERGE. However, I don't see the code using @LineID variable.

Upvotes: 1

Related Questions