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,corrupttext,20 <--- needs to be rejected due to incorrect datatype column 2
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.
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.
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.
As I specified before, MAXERRORS does not do the trick.
I already checked BOL; seems that there is no other option available.