Fastest way to insert lots of rows in MSSQL 2005

growse

growse

growse

Soldato
Joined
18 Oct 2002
Posts
7,139
Location
Ironing
I've got a database on which I need to generate a meta table thingie. Problem is, the table I'm generating is about 300,000 rows, so I need to know what's the fastest way to insert a large number of rows into a blank table.

Currently, I'm building the inserts as a single transaction into a proxy table, dropping the main table and then recreating the main table as a select of the proxy table, before finally re-creating the indexes on the main table. This allows lookups to still occur on the main table as the lock only happens for a very small amount of time.

The inserts into the proxy table are quite slow, and whilst this isn't necessarily causing any performance problems in the app, I want to be sure I'm doing it in the most efficient way.
 
Depending on where your data is coming from, BULK INSERT might be of some use.

If I've got this correct you're basically recreating the same table each time you do this load. Could you then instead load the data into an intermediate table that has the same schema (and indexes) as your main table, then drop the main table and simply rename the intermediate table to become the main table?
 
That is, pretty much what I'm doing. Renaming / copying the proxy table to the main table is very very fast. The slow bit is the actual insert to the proxy table.

I've looked at bulk insert, but doesn't that require the input file to be on the local disk of the database server? Not sure if that's possible as the db is on a different box to the server executing the inserts....
 
You must log in or register to reply here.
Back
Top Bottom