Fastest way to populate a table? 1

[PManion]

I am searching for a rapid way to populate a SQL Server table. I am working in an application that manipulates data in ADO record sets, and then writes the data to disk with a call to UpdateBatch. The ADO code handles this by creating a SQL INSERT statement for every record in the table. This approach is too slow for tables with thousands of records. I am trying to find a faster way to insert a lot of data into a single MS SQL table at one shot. Are there any other procedures that will allow this? Is the table structure significant? Do certain types of indexes or primary keys significantly affect the data insertion rate? Are there ways to import data from text files, or to export into them?

 

[cheyney]

you could write all your data to a text file and then use a dts package to import it. This would probably be faster than executing 1000s of insert statements. If you run through the data import wizard with a sample text file, the last step will give you the option of creating a dts package to automate the process you just defined. You can then upload your created file to the server and use a stored procedure to execute the dts package.

cheyney

 

[PManion]

Thank you, Cheyney. I had forgotten about DTS. I will give it a try.