x

How to handle an Insert error

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
more ▼

asked Jul 03, 2012 at 03:58 AM in Default

satya gravatar image

satya
361 18 18 22

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 03, 2012 at 07:46 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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, 2012 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, 2012 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, 2012 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, 2012 at 09:16 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x21
x1

asked: Jul 03, 2012 at 03:58 AM

Seen: 954 times

Last Updated: Jul 05, 2012 at 09:16 AM