I have a Sql Server table which I am updating from  VFP Tables. It curently has c 200,000 records and is taking about 2 hours to update.

The Sql table consists of about 20 fields from a VFP table, and an additional 10 fields, whcih are in effect lookup fiuelds to give descriptions from a series of VFP lookup tables.If I strip out the 'Lookup Code' bit, the update takes about 5 minutes, so I am fairly sure the bottleneck is in the way I am updating the descriptions, and womder if there is a quicker way to do this update.

I have the following code replicated 10 times for each of my lookup fields, within my main loop to update the main data

1. I set up an OleDbCommand with the connection string and a parameter for the lookup table

2. I then have a While Rdr.read loop to update a field with the description

Is there a quicker/better way to update these descripotions ?

Hi Efhi.

To give meore background.

This is an ongoing requirement..i.e a process that will be run every night, to get data from VFP Tables into a Sql Server Database, which then goes into a BI application.

Most of the data is in a few VFP Tables and these all go into one SQL Table. On this SQL Table, there are say 10 fields, which also need a description. e.g. Rep No (Which needs a description)  Stock Group(Which needs a description)  Area (Which needs a description) Account Category(Which needs a description) etc.. The Sql Table is currenlty at 200,00 records for a 3 month period but this will grow to 1.5 million for say a 2 year period.

I am happy with the main population of the SQl table (Which currently takes about 5 minutes) if I leave out the Descriptions.

I then have to go back over the file (or some other method) and populate each of the six description fields from 6 different VFP tables (Lookup Tables)

(The description population require manily a lookup from the VFP Lookup Tables but some additional processing also,  so I cannot easily use an outer join to these Description Tables)

As I will be doing a lot of this sort of stuff over the coming months, I wanted to get the most efficient way of doing this

I believe that using a stored procedure will be the way to go specialy if this is a process that need to be run more than once. Importing the lookup tables should take no time and will allow the SP to do it's magic.

Edhy Rijo