Re: detect changes on a table

"Artturi" <artturi.nospam_at_free.fr> wrote in message news:4157caf5$0$27298$626a14ce_at_news.free.fr...
| Hans Forbrich wrote:
|
| > Artturi wrote:
| >
| >> Hello,
| >>
| >> I have to create a database (A) based on the content of another one (B,
| >> hosted on oracle) which hosts informations on students (and driven by a
| >> commercial application).
| >> If it will be simple to populate db A initially, I don't see a way to
| >> keep the data in sync with B as updates will occur.
| >>
| >> Do someone here have an idea about that ?
| >> Is there a way to detect updated records for a given table ?
| >>
| >> Thanks in advance,
| >
| > What version of database(s)? What access do you have to the data in B?
|
| Db B's version is oracle 8.1.7. I think I'll have only read access to the
| data in B but this may be negotiated.
| Db A would be MS Sql Server or MySQL
|
| Thanks for your answers, I will have a look at it.
| --
| Artturi

sounds like you're pulling the data from the oracle database table to a non-oracle database table

only if you have some sort of a time stamp or versioning column on oracle database table, then you can just pull rows updated or inserted since your last refresh (there's not built in 'last update' or 'deleted' flag). but you will need to check which rows have been deleted by checking for existence in the oracle database table of every row in the non-oracle database table (that is, at lease every row that has not been inserted or updated)

if you do not have a time stamp or versioning on the oracle database table, and you only have read-access, then you may just have to pull the entire table over to the non-oracle database and totally refresh the non-oracle database table

if you have privilege to create triggers on the oracle database table, you could track inserts/updates/deletes in an audit table, and use that as a basis for your refresh (sort of a build-your own snapshot synchronization)

what are you data volumes? if the are not large, a simple full refresh might be the best bet

++ mcs Received on Mon Sep 27 2004 - 08:52:14 CDT