Rejecting rows with Bulk Insert
-
Is there an easy way to reject rows using bulk insert in for it to continue inserting regardless of some bad data?
For instance (sample 1)
1,224,20
1,225,30
1,corrupttext,20 <--- needs to be rejected due to incorrect datatype column 2
1,320,20
sample 2:
1,224,20
1,225,30
1,3 <--- needs to be rejected due to improper EOF
I hava used MAXERRORS but it does not take care of these issues.
Any help will be appreciated.
-
^^ bump
no one?
-
It's a forum, not on-line help...
Anyway, use the -m parameter followed by the number of errors you wish to allow. Lookup BCP,Overview in Books-on-line for more information about BCP Parameters. You can do some wonderful things with BCP including capturing the errors passed over in an error file for troubleshooting and repair.
--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) -
yes, I have used BCP and I do use it every day.
The problem is that we have production procedures running under VBscript, which runs sql (opening a connection to the db). I am not sure if it will be easy to change BULK INSERT with BCP. The processes insert into a #temp table, which as you know is used by the running instance only.
-
In that case, Bulk Insert has a MAX ERRORS setting that works just like the BCP -m parameter. Again, check BOL under Bulk Insert.
--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) -
As I specified before, MAXERRORS does not do the trick.
I already checked BOL; seems that there is no other option available.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply