MERGE statement Improvements for Batch process error handling
3/27/2009 2:19:23 PM
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?
MERGE [Profile].[Users] AS Users
USING @inputDataTable AS DataSource
ON Users.UserId = DataSource.UserId
WHEN MATCHED AND (Users.DataVersion = DataSource.DataVersion) THEN
UserId = DataSource.UserId
, Status = DataSource.Status
WHEN NOT MATCHED BY TARGET THEN
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.