CODEWITHSUNDEEP
sqlsql-servertry-catchsql-insert
Nils
Nils

Reputation: 514

Try ... Catch ... Insert Into

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?

Upvotes: 4

Views: 21585

Answers (4)

Roshna Omer
Roshna Omer

Reputation: 721

This blog helped me here is some sample code:

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 3

mxix
mxix

Reputation: 3659

If any row in your insert fails all rows that would be inserted in that batch will also fail.

--What to do here? .... (select the same query as up?)

So.. yes.. the same query because all the rows just got redirected even if just one failed.

What reasons do you have to think they will ever fail? Primary keys? Foreign Keys? Any other Constraint or trigger? If that is the case you can do a pre-check on your dataset for those missing keys and save those lines to the error table, and insert the rest and you won't even need the try catch.

EDIT:

In those columns that are nvarchar and should be INT you can filter the rows where patindex('%[^0-9]%',<YOUR COLUMN>) > 0 and you will get all rows that will fail the datatype conversion. Insert those rows to error. the rest to your correct destination table.

Upvotes: 1

Shiju Shaji
Shiju Shaji

Reputation: 1730

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

Upvotes: 5

Related Questions