Since you are committed, you might have more luck if you can post the manufacturer's name and see if anyone on the UT has actually worked with their data before in either version. I find it hard to believe the manufacturer does not provide a data migration tool for their own products! Someone has had to have written something already or else why would anyone upgrade?




>>It seems that delay is entirely ODBC-origin. I see two things to try.
>>1) Send INSERT Into newtable ... SELECT ... From oldtable command through SQLEXEC(). Maybe it will work.
>>If not then
>>2) Maybe you could rethink the whole idea. Why do you need to move data to new table? Maybe you can just create new field(s) in existing table and use them.
>>
>Edward, the systems are two separate manufacturing packages. Both from the same company... but one is newer than the other. The user is upgrading the old to the new. The supplier of the package does not provide a migration tool.
>
>The issue is the difference between the two is quite substantial. There are more fields in the new system, field names have changed, etc. That is why I'm doing the transfer via ODBC. They do provide the ODBC drivers for both systems.
>
>Basically I'm writing the data migration tool. And I seem to be having success in transferring the data, but it is way too slow! I will not be able to transfer data over a weekend.
>
>The reason I got the contract, and I'm now taking a bath on it, is the supplier offered to do it, but it would take 4-5 days minimum! And the users would need to be shut down for that time. I am trying to write something that would do the same thing over a weekend.
>
>Any ideas on how to extridite myself, cleanly and without getting burned even more? If it is ODBC related, I can't see how I'm going to be able to do it in the timeframe required.
>
>Thanks,
>Mike
>
>
>>>>If I understand correctly you move data from foreign source, change it in VFP and then move back to the source? Does this 'source' have any kind of own processing capabilities, like stored procedures?
>>>
>>>No, there does not appear to be any stored procedures in the database system.
>>>Mike
>>>
>>>>
>>>>>>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.
>>>>>-------------------
>>>>>Sorry, I wasn't clear here. The data is not VFP data. It is some form of propietary file format... looks like text delimited, but there are control characters at the start... The product supplies ODBC drivers for each. Since one system has differnet field names than the other ,and sometimes the data type has changed, I need to marry the fields and move them over.
>>>>>
>>>>>i.e.
>>>>>Job_No is now Job
>>>>>Cust_no is now Customer
>>>>>
>>>>>Some fields were smallint data type are now int, etc.
>>>>>
>>>>>All of these are taken care of in the routine that gets the lcValueList
>>>>>----------------------
>>>>>
>>>>>So basically I'm asking is there anyway, via parameters for the /sql or otherwise to speed up this transfer. I really am starting to believe the stumbling block is the ODBC drivers, not my code.
>>>>>
>>>>>Mike
>>>>>
>>>>>
>>>>>>
>>>>>>>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