Speeding up bulk inserting in SQL Server 2005

Question

Is there any way to speed up bulk inserting in 2005? I'm looking into partitioning, is it good?

Answer 1

Here's a similar question which addresses the same issue to a certain degree:

http://stackoverflow.com/questions/24200/whats-the-fastest-way-to-bulk-insert-a-lot-of-data-in-sql-server-c-client

Answer 2

See Loading Bulk Data into a Partitioned Table for a discussion of Bulk Insert and partitioning interaction. While partitioning does not speed up bulk insert, it allows you to bulk insert parallel loads into separate unpartitioned tables and then use ALTER TABLE ... SWITH PARTITION to transform all uploaded into a single partitioned table, see Transferring Data Efficiently by Using Partition Switching. However to do this it is required that the data is properly partitioned in the ETL process first, so that is sent to the proper bulk insert load.

In most cases, going to such extremes is not necessary. You should be able to obtain impressive bulk insert speeds in simple scenarios w/o problems, you sure you squeezed everything out from the plain ole' bulk insert? For instance, are you sure that bulk-logging occurs? And not last, is your disk fast (both LDF and MDF)? Are we talking about 20 spindles in stripped RAID 10, or is a 5400 RPM 1TB slow-as-it-gets disk?