Try ... Catch ... Insert Into

Question

I'm trying to insert all the errors that I get from my select statement into an error table but I don't get this to work.

BEGIN TRY 
    INSERT INTO [database]..[Table]([Column1], [Column2], [Column3])
       SELECT 
           [Column1], [Column2], [Column3] 
       FROM 
           [database]..[SourceTable] 
END TRY
BEGIN CATCH
     INSERT INTO [database]..[ErrorTable]([Column1], [Column2], [Column3])
        -- What to do here? .... (select the same query as up?)
        SELECT 
            [Column1], [Column2], [Column3] 
        FROM [database]..[SourceTable]
END CATCH

Now I would like to see all the errors in my Error table and all the once that got right in the Table but i dont get this to work?

The datatypes of source system is nvarchar and my columns are INT because the values should only be INT.. so it´s bad design from source system?

Answer 1

Try This

Begin Catch 

INSERT INTO [database]..[ErrorTable]
(
[Column1],
[Column2],
[Column3],
[Column4],
[Column5],
[Column5]
)
SELECT
    cast (ERROR_NUMBER() as <your datatype>)
    ,cast (ERROR_SEVERITY() as <your datatype>)
    ,cast (ERROR_STATE() as <your datatype>)
    ,cast (ERROR_PROCEDURE() as <your datatype>)
    ,cast (ERROR_LINE() as <your datatype>)
    ,cast (ERROR_MESSAGE() as <your datatype>)
End Catch

Answer 2

I suggest you work on fixing the problem, rather then trying to capture the errors. So, look for the values that do not match, something like this:

SELECT [Column1], [Column2], [Column3] 
FROM [database]..[SourceTable] 
WHERE Column1 LIKE '%[^0-9'%] OR
      Column2 LIKE '%[^0-9'%] OR
      Column3 LIKE '%[^0-9'%];

If you are using SQL Server 2012, then use try_convert():

SELECT [Column1], [Column2], [Column3] 
FROM [database]..[SourceTable] 
WHERE try_convert(int, Column1) is null OR 
      try_convert(Column2) is null OR
      try_convert(Column3) is null;

You can use a similar statement to just insert the correct data.