Home Dashboard Directory Help
Search

MERGE statement Improvements for Batch process error handling by TFarrell


Status: 

Active


1
0
Sign in
to vote
Type: Suggestion
ID: 427763
Opened: 3/27/2009 2:19:23 PM
Access Restriction: Public
0
Workaround(s)
view

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.
Details
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.
Sign in to post a workaround.