CODEWITHSUNDEEP
sql-serverdebuggingmerge
Oak_3260548
Oak_3260548

Reputation: 2010

Debugging SQL MERGE

I would like to debug the following code:

MERGE PlDayCap AS TGT
USING (SELECT MID, CalDate, SUM(CapAva) as aTime 
       FROM PSP 
       WHERE CalDate > DATEADD(DAY, -5, GETDATE()) 
       GROUP BY CalDate, MID) AS SRC(MID, cDate, aTime) ON (SRC.cDate = TGT.cDate AND SRC.MID = TGT.MID)

WHEN MATCHED 
    THEN  
       UPDATE SET TGT.aTime = SRC.aTime

WHEN NOT MATCHED BY TARGET
    THEN INSERT (cDate, MID, aTime, uTime) 
         VALUES (SRC.cDate, SRC.MID, SRC.aTime, 0)      

WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

which runs, but doesn't return the expected results.

The target table is empty at the moment, so a NOT MATCHED BY TARGET part should be executed, but it doesn't work and I am not even able to say, whether it is because the INSERT statement has not been called, or if there's an error in this statement.

I usually use following SQL debugging methods:

But nothing is available within MERGE conditions, where one can only have DELETE, UPDATE or INSERT statements and work only with defined source and target tables, as far as I know.

I know that SQL debugging options are poor, but I am hoping to find a way through...

Upvotes: 0

Views: 2431

Answers (1)

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

In this query, are you sure SRC table has valid records that will allow merge.

You can use OUTPUT clause with MERGE to check the results as soon as they are run.

OUTPUT Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.

MERGE PlDayCap AS TGT
USING
    (SELECT MID, CalDate, SUM(CapAva) as aTime FROM PSP WHERE CalDate > DATEADD(DAY,-5,GETDATE()) GROUP BY CalDate,MID) 
    AS SRC(MID, cDate, aTime) 
ON (SRC.cDate = TGT.cDate AND SRC.MID = TGT.MID)
WHEN MATCHED THEN 
    UPDATE SET TGT.aTime = SRC.aTime
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (cDate, MID, aTime, uTime) VALUES (SRC.cDate, SRC.MID, SRC.aTime, 0)        
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;

Upvotes: 2

Related Questions