MERGE statement Improvements for Batch process error handling - by TFarrell

Status : 

 


1
0
Sign in
to vote
ID 427763 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 3/27/2009 2:19:23 PM
Access Restriction Public

Description

Using the merge statement to update/insert records using a table valued parameter, if an exception is thrown during execution, the batch fails.

Is there any way to determine which row of the batch caused the failure?

For instance:
MERGE [Profile].[Users] AS Users 
USING @inputDataTable AS DataSource 
ON Users.UserId = DataSource.UserId 
 
WHEN MATCHED AND (Users.DataVersion = DataSource.DataVersion) THEN 
     UPDATE  
        SET  
            UserId  = DataSource.UserId 
            , Status = DataSource.Status 
 
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (             
            UserId 
            , Status 
            ) 
    VALUES ( 
            DataSource.UserId 
            , DataSource.Status 
             ) 
 
OUTPUT DataSource.RowId, Inserted.UserId, Inserted.DataVersion, 0 AS ErrorCode INTO @operationLog; 

The above works great for single rows, or for errors like optimistic concurrency that don't throw an exception because I compare @operationLog and @inputDataTable to determine rows that failed due to concurrency.

However, if an exception is thrown, say violation of PKey constraint, in the middle of a 1000 row batch, the batch is doomed, and that is fine, but there seems to be no way to determine that row 500 caused the exception.
Sign in to post a comment.
Posted by Microsoft on 4/17/2009 at 9:53 AM
Thanks for reporting it. We will look into it in a future release.