CODEWITHSUNDEEP
asp.netsqlsql-server
Maksim Vi.
Maksim Vi.

Reputation: 9225

Insert multiple records in one query

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?

Upvotes: 2

Views: 1152

Answers (4)

M6rk
M6rk

Reputation: 159

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.

Upvotes: 1

Al W
Al W

Reputation: 7713

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.

Upvotes: 2

Jay
Jay

Reputation: 57919

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

Upvotes: 1

LesterDove
LesterDove

Reputation: 3044

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

Upvotes: 3

Related Questions