fast, consistent way to bulk insert?

I'm looking for a way to insert 50k records into a SQL Server table, and need to get it done faster. right now using BULK INSERT takes 5-10 seconds, but faster would be better, and even better if it were a consistent amount of time.

I've heard of DTS but don't know quite how to use it - would be offer any performance gains? any clue what the bottleneck is for BULK INSERT? hard drive speed? amount of RAM (this was on a 512mb machine)? parsing the fields?

thanks for any ideas.512mb is rock bottom for running a production SQL box. Are you appending these records to an existing table? Is it something you're recreating every time? There's other options available if you're starting from scratch each go...|||...and as I understand it Bulk Insert is faster than anythting DTS can offer. BCP? I don't know about comparisons of BULK INSERT and BCP - maybe oters do.

Also you can try inserting into another table and then inserting from that. If you validate the data in the intermediary table you can use the NOCHECK option on constraints to speed up inserting.|||There's a comparison of bulk insert methods here. All the comparisons are for 2005 though.

http://weblogs.sqlteam.com/mladenp/archive/2006/07/17/10634.aspx|||What I have heard is that BULK INSERT is slightly faster than bcp mainly because bcp is a client side utility and BULK INSERT is internal to SQL Server. I could be wrong.|||Want faster imports?

Drop the indexes and use bcp|||thanks for the suggestions, I'll check them out. 512mb is definitely low, and will be upgraded once approved (ah bureaocracy)

this bulk insert will actually be done several times during the day, adding to the table instead of updating, and has to be able to be initiated from code (i.e. .NET app). looks like SSIS is something to check out.|||also, if you use bcp it can be considerably faster if you specify the TABLOCK hint in your query. You can pass the TABLOCK hint using the -h flag.

More tips here:

http://www.databasejournal.com/features/mssql/article.php/3095511