Mike,

In your original you say "I am moving data from one system to another via ODBC. (Both systems are not directly compatable.)".

Is this data sitting in a non-VFP table, or is it VFP-style data? I think we (well, me anyway) have been assuming that you were talking about VFP data here.


>To the people looking for code on how I do this:
>
>The read is thus:
>lcTableName is the name of the table I want to get the data from
>lnAgamaHandle is the handle returned by the opening of the AgamaLink
>lnResult is the result from the query, to trap for errors.
>lcSql is the SQL string to execute
>
>lcSql='SELECT * FROM '+lcTableName
>lnResult=SQLEXEC(lnAgamaHandle,lcSql,'Agama')
>
>To get the value for lnAgamaHandle
>
>SQLSETPROP(0,'DispLogin',3) && No Password req'd
>lnAgamaHandle=SQLCONNECT('Agama','DBA','')
>
>
>The above SQLEXEC command takes about 2 hours to execute to get about 27,000 records. AS you state, this is unacceptable.
>
>------------------
>After the data is retrieved, I have a translation table that holds the source field name, source table, target field name and target table. I run a routine that places the field names into a 2 dimesional array, first column is the source, second is the target. This the list of fields for the SQL INSERT command. (It is only run once.)
>i.e. lcFieldList
>lnSysproHandle is gotten in a similar manner to the lnAgamaHandle above.
>lcSql is the insert command to be executed
>lcValueList is the values to insert.
>lcTgtTable is table to insert into
>
>Then I run through the source table, the result of the first query.
>
>Example of the code is below
>lcFieldList=GetFieldList()
>Select Agama
>SCAN
> scatter memvar
> * GetValues is a routine that translates the memvars to the data for the new system.
> lcValueList=GetValues()
> lcSql='INSERT INTO '
> lcSql=lcSql+lcTgtTable+' ('+ALLTRIM(lcFieldList)+') '
> lcSql=lcSql+'VALUES ('+lcValueList+') '
> *- Now Write Data To Target Table - lnResult to handle errors
> lnResult=SQLEXEC(lnSysproHandle,lcSQL)
>ENDSCAN
>
>This usually takes about 1/2 or less time to execute than the read above.
>
>
>Is there anything else I could do to optimize/improve the speed of retrieval and write?
>
>TIA,
>Mike