I am using Visual BASIC 6 and ADO to access data from two seperate databases (using ODBC) - read selected data from one database and store the result in the second database. At present, both databases are stored in Progress 9.1d and housed on the same server
I can read/write data from each of these databases seperatel with no issues so my conection and recordset configuration is OK - create two seperate connection string and recordset
How do I (or is it possible) to use a SQL Statement (either INSERT INTO or UPDATE) to read from Database 1 and write/update Database 2 - I would prefer to use SQL command rather than to a programmed loop within VB
For example - if Table1 is stored in Database1 and Table2 is stored in Database2 then how do I structured the SQL for an update.
UPDATE Database1.Table1, Database2.Table2 Set Database2.Table2.Data = Database1.Table1.Data WHERE (Database1.Table1.Key = Database2.Table2.Key)
I believe the only way that will work is if Progess supports it. For example, in SQL Server, you can register remote servers (including standard ODBC databases), and then SQL server will coordinate the data across servers (it is horribly slow however).
ADO itself will not do it, since a SQL statement runs on a single connection to a single DB provider.
As I understand the request: You want to take the data from D1.T1 and migrate in bulk to D2.T2. If that is the case then you may find the following SQL helpful: (I am a SQL Server programmer but I believe the code is ANSI-92 compliant)
For updating Existing Data based on Key Match: UPDATE D2.T2 SET Field1 = t1.field1, Field2 = t1.Field2, etc... FROM D1.T1 WHERE D1.T1.KeyField = D2.T2.KeyField
Inserting New Data: INSERT INTO D2.T2(Field1, Field2, ...) SELECT Field1, Field2, ... FROM D1.T1
** If Progress supports the INSERT INTO shorthand then you may do so, it woun't affect the results, minding that the ordinal position of fields MUST MATCH 100% for the shorthand to work. ** I dropped the 'dbo' as I would have used in SQL Server as you do not have that in your sample information.
With the version 9 drivers it is not posible to connect to 2 databases. In version 10.1B it is posible to connect with multiple databases (on the same machine) using the datadirect 5.2 odbc driver.
