Insert multiple records in one query

Question

I am trying to insert about 100,000 in my SQL Server database. It is really slow when I create 100,000 separate queries, so I tried to insert all these records in one query, it worked for the first few thousands records but then it threw me a timeout error.

What would be the fastest way to insert multiple records into database?

Answer 1

Look into bulk insert for starters, wherein you feed the server a raw data file and a schema file describing your data (in a nutshell.)

Answer 2

bulk insert (this handles splitting the insert into manageable batched transactions for you): http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 3

also...don't do this in a web request. you can get around this if you increase the CommandTimeout (different than the ConnectionTimeout), but best practice is to put these massive bulk loads into an out of process job that actually does the loading.

Answer 4

Another possibility (other than the bulk insert) is to adjust the fill factor for the table's indexes. Try a fill factor of 70% on each of the table's indexes and see what that buys you.