INSERT 1000000 posts in database

[ost]

Hi

 

I have a textfile with 1000000 lines and i need to INSERT these into a database.

 

The keyword here is performance. I have tried to read a line and doing an INSERT for each line.. This means 1000000 executenonquery calls and it would take about 1 hour to do this import.

 

Anyone have other ideas on how to do this?

[samsmithnz]

BCP is probably the best bet here. ITs a cmd line SQL app. look it up.

[iebidan]

try DTS or bulk copy, will be faster too

What does BCP means???? Bulk Copy Process or something like that????? :D

[Denaes]

I'd have the program read all of the data from file into a dataset entirely (or in batches if its too much information) and then use the dataAdapter.update method to update more than one record at a time.

 

I don't know if behind the scenes, its really calling 1,000,000 INSERT statements. That may be the case.

 

Thats how I'd do it with my oleDB. I'm not sure about MSDB.

[Derek Stone]

Look for the terms "sql server bulk insert" on Google. Performing anymore than a few consecutive inserts at once without doing a bulk insert is just asking for performance problems.