Home Forums SQL Server 2014 Administration - SQL Server 2014 bcp aborts on first FK violation RE: bcp aborts on first FK violation

  • November 27, 2013 at 1:06 pm

    #1669568

    bruce.malmat (9/26/2013)

    I want to use BCP to load data from a text file.

    By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.

    bcp aborts if ANY of the rows contains a FK violation. No data get loaded.

    So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.

    I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?

    Yes, there is. If you take a look in Books Online for the BCP command, you'll find that the -m parameter changes the number of allowable errors. You can also use the -e parameter to identify a file where the error rows can be stored for validation and rework. Set the batch size back to at least 50,000, set -m to a very large number (like 2 billion), and let 'er rip.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)