How to handle an Insert error

Question

Hi All,

I need to write a logic in one of my stored procedure like below.

While inserting the records into main table if any records causes Primary Key or Foreign Key violations system should insert those error records into other table without raising any error or stopping the flow.

Like :

Main Table :

create table data(id int primary key,val1 varchar(10),val2 varchar(10))

Validation Table :

create table data_invalid(id int,val1 varchar(10),val2 varchar(10))


insert into data(id,val1,val2) 
values(1,'a','b'),(2,'c','d'),(3,'a','e')

This query executes successfully and 3 records inserted in data table.

insert into data(id,val1,val2) 
values(4,'a','s'),(3,'c','d'),(5,'a','e')

This query fails and no records inserted in data table.

As per my requirement I need to write a code that, valid records inserted into data table and error causes or invalid records diverted or inserted into data_invalid table with out raising any error or stopping the flow.

Please give your inputs.

Thank You

Satya

Answer 1

0

you need to use TRY...CATCH logic that will handle the error or the PK/FK violation and then carry out the process you need.

Reference - http://msdn.microsoft.com/en-us/library/ms175976.aspx

answered Jul 03 '12 at 07:46 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

Thank you Jonathan,

But this will not work for my requirement, because if I use TRY..... Catch block this one will run either TRY block or Catch block (runs query or code enclosed between Begin and End statements wise), but I need row level operation.

Any other inputs...

Jul 03 '12 at 09:43 AM satya

In that case you will need to parse your source and identify those that are going to fail and process them differently from the ones that will succeed.

This may mean altering the way to gather the source data - I assume you don't use a VALUES method as per your demo code - or vetting the data via TSQL or other means so that there are not going to be any issues.

Jul 03 '12 at 10:00 AM Fatherjack ♦♦

Fatherjack,

Thanks for your information.

In my code I am using Insert into table select * from t1 join t2 join t3 like this.

As per my understanding you recommend me, to filter the failing condition and then delete it first then remaining records(which will not error out) inserting into main table.

Is it Correct ?

Jul 05 '12 at 09:11 AM satya

If the source is a SELECT then you can add a WHERE to avoid the problem records. If you want to delete them then that is your decision. Make sure the data is not needed by the business. Might you not need to alter those rows so they are usable and pass them to the destination?

Jul 05 '12 at 09:16 AM Fatherjack ♦♦

(comments are locked)

How to handle an Insert error

Follow this question

Related Questions