Best method to format and convert database??

I’m writing an application that needs to be able to pull information from a Foxpro database, format it, and then store it into an Access database.

At the moment I'm using a For Next Loop to pass through each field in a dataset loaded with the foxpro info. In the loop I use an Insert command to store the information into the access file. The problem with this is that the foxpro file has around 150,000 items and when I start the process it almost maxes out my computer. It's also very slow. It has taken about 30 minutes so far and according to the progress bar isn't even half way.

CPU usage runs at about 90 - 95%, and Memory = 142

PC Specs
---------------------------------------------------------------
Processor = Athlon XP2500+ (1.86 GHz)
Ram = 512 DDR 2100
---------------------------------------------------------------

Can anyone tell me a better way to go about this

Answer this question

Best method to format and convert database??

Roberto Santos
We implemented a batched updates feature for .Net 2.0, but this is only for SqlClient. See blog - http://blogs.msdn.com/dataaccess/archive/2005/05/19/420065.aspx

For other providers, like OledbClient, you could try Anjali's suggestion above.
bdee1
Yuo can take couple of insert sqls togethar and have them run as a script like
i = 0
loop

   sql = "begin "
   j = 0
loop
      sql &= " insert into table1.......values('a','b','c');"
      j += 1
   while j < 25
   sql &= " end;"
   ExecuteSql(sql);
   i += 1
while i < 15000

this would do 25 Inserts in one go.
Mikhail Podolski
Can an Insert command be used to insert multiple rows at once I was thinking maybe there was a way to use it similar to the update command That way I wouldn't have to use the loop to update row by row.
Best method to format and convert database??

Answer this question