SQL Server Performance

Wouldnt insert keep going after an error?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Pugnax, Jan 31, 2007.

  1. Pugnax New Member

    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?
  2. Adriaan New Member

    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.
  3. Pugnax New Member

    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!
  4. Adriaan New Member

    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=':)' />]
  5. Pugnax New Member

    Adriaan,<br /><br />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.<br /><br />WHERE keyword? What is it? And... what is a keyword? <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Thanx.<br /><br /><br />
  6. Adriaan New Member

    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.

Share This Page