November 1, 2010 at 2:29 am
Need help handling errors while running a merge statement.
How to I make the merge behave as if it has a try-catch block surrounding each record processed
( If an error is encountered, catch that error and log it (or any other processing) then continue processing the next records)?
What is the default behavior of the Merge statement, does it rollback the whole transaction when an error occurs or it continues processing the records?
Can the output clause be used to check for errors?
November 1, 2010 at 8:53 am
devora.fs (11/1/2010)
Need help handling errors while running a merge statement.How to I make the merge behave as if it has a try-catch block surrounding each record processed
( If an error is encountered, catch that error and log it (or any other processing) then continue processing the next records)?
What is the default behavior of the Merge statement, does it rollback the whole transaction when an error occurs or it continues processing the records?
Can the output clause be used to check for errors?
The only way to wrap a try-catch block around the processing for each row is to process each row separately. When trying to insert/update an entire set of rows, the set fails or succeeds as a single transaction. Consider checking your input table for the most common errors prior to the MERGE.
The OUTPUT clause cannot be used to check for errors since it can only be used to trap information based on rows affected by an INSERT, UPDATE, DELETE, or MERGE statement. If there is an error, no rows are affected.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 1, 2010 at 12:10 pm
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply