SQL Server Performance Forum – Threads Archive

Wouldnt insert keep going after an error?

Folks, I’m doing a big insert (many rows being inserted), and I dont want it to stop the whole process due to a couple of errors. I’d like it to commit the completed inserts, and ignore those which have failed. Is there a way of doing this without using try/catch?

What type of errors? If duplicates, then make sure you’re inserting rows with unique key values for the target table, with a NOT EXISTS check to make sure that these key values do not already exist. If you’re inserting NULLs into non-nullable columns, then figure out what the problem is, or substitute the null using the ISNULL() function. Look at your data, figure out why tables have constraints, and look at your data again.

Adriaan, Actually I was doing some inserts in a table with a foreign key, whose column was sometimes being set with undefined values. Whenever this happens, the insert was supposed to be ignored, without stopping the whole process (the other sucessful inserts should be commited). I’ve found out a way: SET XACT_ABORT OFF. It makes the process not to be aborted in the middle, if an error occurs. Thanx for the reply!

Then make sure you do not insert rows where the FK value is undefined – ever heard of the WHERE keyword?[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Adriaan, I dont need to, the engine does it for me. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> That’s why to use set xact_abort_off: whenever the value is "defined", it will work. Otherwise, it simply wont do anything, but wont abort the whole process. WHERE keyword? What is it? And… what is a keyword? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Thanx.

You’re not really solving a problem by ignoring the error. You should handle foreseeable errors like these in your code. So use a WHERE clause for your INSERT query to eliminate rows where the FK value is unknown.

]]>