Improve Bulk Import With bcp.exe 1

[rebelknight]

We need to import lots of data to SQL server 2005.
BCP is recommended everywhere as the fastest method.

Triggers and constraints are not applied by default but does it improve performance to still drop constraints and triggers during the import?

Various sites talk about turning on the 'SELECT INTO/BULKCOPY' function to speed up importing.
I can't find this option defined anywhere.
Where can I set it?

 

[mrdenny]

You shouldn't get any performance increase by dropping the constraints and triggers as they are bypassed.

The 'SELECT INTO/BULKCOPY' option is an old SQL 7 option. The SQL 2000 and SQL 2005 equilivent would be to set the database recovery model to BULK LOGGED.

This can help because it stops SQL from logging the bulk inserts. If you don't want to change the logged level of the database you can use the -b option of the BCP command to batch the inserts to keep the transaction log from going out of control.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[rebelknight]

That explains it.
The we ahve are a bit out of date and I could not find much info on books online.