Thanks to Nick Sertis for this trick – who knew TSQL could do try/catch statements!
Very useful when you need to write data manipulation scripts for production databases.
BEGIN TRY
BEGIN TRAN
--Some SQL
COMMIT TRAN
END TRY
-- Catch the errors on the inserts
BEGIN CATCH
ROLLBACK TRAN
SELECT ERROR_MESSAGE()
END CATCH