Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012

    Unanswered: Skip FOREIGN KEY violations during INSERT operations

    Hi all!

    I'm trying to optimize a few batch import procedures we use in our processes.

    It currently works like this:

    1) Cursor loop cycles through all data to be imported from IMPORT table
    2) For every record there is an attempted insert to PROD table in a TRY-CATCH check to see whether the record would pass all the primary key and foreign key constrains in PROD table
    3) Only those that pass the TRY-CATCH check gets imported into PROD table
    4) Every row gets logged into a separate LOG table, either with a comment like "Import OK" or "Error: foreign key violation in field 'my_id'"

    The thing is, the procedure runs fine when I'm importing several thousands of records, but when it comes to hundreds of thousands, the speed becomes an issue, as I currently get 20 records per second and slowing...

    There is no other code in that procedure, no queries. Just the Cursor cycle and the try-catch check.

    I'll be glad for any ideas to optimize the logic.

    Many thanks,


    PS: I'm using MS SQL Server 2005.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    1) Import all of the records into a staging table with no constraints.
    2) Insert into the logging table any record that does not have a parent, or is a duplicate (i.e. does not pass validation) this will be two queries.
    3) Insert into the tables any rows that pass the validation
    For a few hundred thousand rows, this should only take on the order of minutes, depending on hardware, number of constraints checked, etc..

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Similar to Crowley's method:

    Create a column in your staging table named "ImportErrors"?
    Run set-based (not cursor) operations to identify records that do not meet validation requirements, and update the ImportErrors column with the name of the first test they fail.

    When you are done with validations, import all the records without import errors into your production table.

    This is my standard method for ETL.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  4. #4
    Join Date
    Nov 2012
    Hi blindman, MCrowley!

    Many thanks for your input!

    I was hoping there would be a way to do it without having to specify the constrains in the import procedures again (first in the table constraints, then in the import queries).

    I understand that this is a clean solution, but I wished there was maybe a less elegant solution. I know that in MS Access when inserting records into table with constrains, the records that would violate the constrains would be silently skipped. Usually, that is a bad thing, but I would appreciate the behavior this time.

    Thanks again, I will use the query solution!

  5. #5
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Have you considered using Microsoft Access to import the data from your text file and store it in your SQL Server? If you already have Microsoft Access, it is probably the quickest and easiest way to get what you want.

    You might also be able to get SSIS to handle this kind of "messy processing" for you. I think that it would at least be worth checking.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts