TSQL Merge error handling

Question

Need help handling errors while running a merge statement. How do I make the merge behave as if it has a try-catch block surrounding each row processed ( If an error is encountered, catch that error and log it (or any other processing) then continue processing the next rows)? 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?

Answer 1

MERGE, as with all other DML statements (INSERT, UPDATE, DELETE) is an all or nothing proposition - either it succeeds in it's entirety, or it fails (and the implicit transaction it's running within, if such a one has been opened, is rolled back).

If there are going to be actual errors (e.g. broken foreign key constraints, check constraints, truncation), you need to deal with these beforehand, or alter your MERGE so that the error will not occur (e.g. explicitly truncate overlong data). If the source of your MERGE statement is a complex query, and the data cleanup is likely to be complex, you might want to stage the results into a temporary table to perform such cleanup.