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

Rejecting rows with Bulk Insert Expand / Collapse
Author
Message
Posted Thursday, April 22, 2004 7:43 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:34 PM
Points: 67, Visits: 177

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.




Post #112547
Posted Thursday, April 22, 2004 3:56 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:34 PM
Points: 67, Visits: 177

^^ bump

no one?




Post #112667
Posted Thursday, April 22, 2004 7:20 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159

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."

(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 #112677
Posted Friday, April 23, 2004 7:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:34 PM
Points: 67, Visits: 177

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.




Post #112738
Posted Friday, April 23, 2004 9: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 @ 2:50 PM
Points: 36,711, Visits: 31,159

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."

(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 #112902
Posted Monday, April 26, 2004 7:19 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 3:34 PM
Points: 67, Visits: 177

As I specified before, MAXERRORS does not do the trick.

I already checked BOL; seems that there is no other option available.




Post #113035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse