If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append new data from different database
Hi.
I need to periodically append new data from another database (located in different country). Now I'm using ADO (with DSN) to download whole tables (INSERT INTO xxx SELECT ...). My question is: how to select only specific records on the remote side to avoid downloading big tables? My tables have complex, multi-field keys. So I was thinking: SELECT * FROM RemoteTable WHERE RemoteTable.ALocalTable.A AND RemoteTable.BLocalTable.B It won't work because my query is executed in ADODB connection (so on the other side). How can I automatically synchronize my base with remote one? I'm using Access 2007, remote db is Oracle. Thanks for help. Kamil |
#2
|
|||
|
|||
Append new data from different database
On Fri, 19 Mar 2010 09:50:14 -0700 (PDT), Kamil
wrote: Hi. I need to periodically append new data from another database (located in different country). Now I'm using ADO (with DSN) to download whole tables (INSERT INTO xxx SELECT ...). My question is: how to select only specific records on the remote side to avoid downloading big tables? My tables have complex, multi-field keys. So I was thinking: SELECT * FROM RemoteTable WHERE RemoteTable.ALocalTable.A AND RemoteTable.BLocalTable.B It won't work because my query is executed in ADODB connection (so on the other side). How can I automatically synchronize my base with remote one? I'm using Access 2007, remote db is Oracle. Thanks for help. Kamil I've been thinking about this for a day or so and I can't think of any *good* way that doesn't require sending a lot of information up or down the wire. A Passthrough query would at least put the load on the remote Oracle database (which I expect can handle it easily, though the DBA might not appreciate the suggestion!), but if you need to upload *your* entire table every time to do the comparison, you're not ahead. My only suggestion would be to at least try doing the query in a "frustrated outer join" rather than the not-equals WHERE, since the latter will probably force a full table scan: SELECT RemoteTable.* FROM RemoteTable LEFT JOIN LocalTable ON RemoteTable.A = LocalTable.A AND RemoteTable.B = LocalTable.B WHERE LocalTable.A IS NULL; This will find all records in RemoteTable which do not exist in LocalTable (based on the keys), but of course will not find records which exist in both tables but have been changed. The query optimizer might be able to come up with a better plan, but if LocalTable is very large it will still require a lot of data be moved. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|