Transfering data between 2 database types

[mbutch]

I'm writing a Windows Service in VS2003 using C#. What I'm trying to accomplish is extracting information from a Visual FoxPro 6 Database and sending all of that information into a SQl Server 2000 Database. I can't just use a DTS package because a lot has to be done with the VFPro data before it's ready to be transfered.

This will occur once every day. I want to delete all info from the sql table, get the vfpro data the way I want it and insert it into the sql table.

I've never used ADO or ADO.NET, so I wasn't sure exactly what approach to take. The first approach I took was to create an odbcCommand to retrieve the data how I wanted it, put it into a DataReader, loop through the results and insert them one by one into the sql table. This didn't seem like the best way to do it so I tried putting all of the data into a DataSet. I thought it would be easier to put all of the info into a dataset and then use the dataset to populate my sql table. It doesn't look like it's just that simple.

Any ideas would be appreciated. Does anyone know of a good book that deals with these types of issues?

Thanks

 

[palbano]

You could write the data to a disk file and then execute a BULK INSERT

-pete