Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

bcp aborts on first FK violation Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 AM
Points: 1, Visits: 1
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?
Post #1498888
Posted Thursday, September 26, 2013 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 3,535, Visits: 7,620
I'm not sure if bcp would allow you to do that. However, you could load all data to a staging table and insert clean data into your final table to avoid PK violations.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1498917
Posted Wednesday, November 27, 2013 1:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 36,978, Visits: 31,498
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1518194
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse