Hi Dan,
Thanks for the reply. The VFP data that I was after required a significant amount of "massaging" - so I wrote a VB6 app to bring in 100,000 records at a time (using the VFP oledb provider), parse the data, and create a text file that I used in a Bulk Insert command. I did 86 iterations of this (which was, of course, automated) until all 8.6 million records had been extracted from the VFP table and intserted into the SQL Server database.
The program took just over 4 hours to run. But it worked and that was the goal.
Interestingly, the application that accesses this SQL Server database allows the users to perform adhoc queries - in which I build the "where" clause dynamically and pass to a stored proc. I thought that with over 9 million records to query (after the VFP data import) that the queries would be noticably slower - to my surprise, the processing time difference was very insignificant so I didn't have to tweak the indexes already in place.
Regards,
Al
>If you are moving data from VFP into SQL Server and don't have to change the data much, the absolute fastest way is via DTS using the VFP OLE-DB driver.
>
>I don't know if MSDE includes the DTS DLL's, but if not, I think you can install them separately on your client machines that use MSDE. Check the SQL Server BOL regarding files required and licensing. DTS has a decent object model that you could take advantage of in a very small VFP or VB loader app.
Al Williams
Anola MB, CANADA