Hi
I would use a prepared SQL statement for greater speed.
I don't know how Oracle works, but maybe setting manual transaction handling for write connection and a SQLCOMMIT() after 500/1000 insert operations can speed things up (at least this is the case for Interbase/Firebird because record versioning)

You can do something like this
Get records from source table in source database

>SqlExec (nOrigTableHandle, "Select * from " + cTableName + "... where " + cWhereClause, "TempCursor")

Generate a SQL command based on columns and execute it for the destination database connection.

lnFields = AFIELDS(laFlds)
SET TEXTMERGE on
SET TEXTMERGE TO memvar lcInsertDataSQL NOSHOW 
\\INSERT INTO <<tcTable>> (
FOR i=1 TO lnFields
	IF i>1
		\\, 
	ENDIF
	\\<<laFlds[i,1]>>
NEXT
\\) VALUES (
FOR i=1 TO lnFields
	IF i>1
		\\, 
	ENDIF
	\\?m.<<laFlds[i,1]>>
NEXT
\\) 
SET TEXTMERGE to
SQLPREPARE(nConnHnd,lcInsertDataSql)
SCAN
    SCATTER MEMVAR 
    IF SQLEXEC(nConnHnd) < 1
	CLEAR
	AERROR(aError)
	DISPLAY MEMORY LIKE aError
	?'insert error'
	SUSPEND
    ENDIF
ENDSCAN

>Hi,
>
>I need to create a VFP program that will copy selected records from one table to another (via SPT). For that purpose I must open two different ODBC connections, because the origin tables and destination tables reside in different Oracle databases.
>
>The origin and destination tables have the same structure. This program will be in charge of extracting selected records from a production database to a test database.
>
>If I new, in advance, the tables involved think it should be easy, but the issue is that the copy program will read parameters from a VFP table contaning wich tables to copy (table name), and what is the criteria to extract records from the origin table (the "where" clause).
>
>I thought doing something like the following (for each table to be copied):
>

>SqlExec (nOrigTableHandle, "Select * from " + cTableName + "... where " + cWhereClause, "TempCursor")
>
>Select TempCursor
>Go     Top
>
>Scan
>
>*   Here I must move each field of each row of TempCursor to temporary variables
>
>    . . . . (?)
>
>*   Here I must build a string with the Insert SQL
>
>    cInsertSqlString = "Insert Into " + cTableName + "...(?)..."
>
>    SqlExec (nDestTableHandle, cInsertSqlString, "")
>
>EndScan
>

>What is the best way to do this?
>
>Any ideas will be highly apreciated.
>
>Regards,
>
>Fernando