Catch errors and log in SQL Server 2005

Question

I have big script with many INSERT operations. Some of them maybe have mistakes, so I want to write them into a temp table.

What is the best way to do this?

Answer 1

SQL Server 2005 XML Support, Exception Handling, and More

MSDN Webcast: T-SQL Exception Handling in SQL Server 2005 (Level 200)

Answer 2

I would use something like a try catch block with SQL 2005

Example quoted from the site:

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO