Database Q for experts 1

[NicoleM]

hi

I have a situation where a database is constantly updated with new records - no records are deleted, only added. I may not modify the database. The added records need to be copied to another system. how can I keep track of which records have been copied to the other system, as I may not duplicate the records on the other system and may not query that system.

What is the best possible way to do this? can I keep a duplicate of say the primary keys in a second database and write a query to compare the first with the second and extract only the non-duplicated keys? What happens when the database becomes MASSIVE?

thanks!

 

[codestorm]

If you're using SQL Server I'd suggest transactional replication (publish/subscribe) codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
<insert witticism here>

 

[NicoleM]

Sorry but one database will possibly be Dbase and the other will be a completely different database.

 

[zemp]

If you can't change either database (ie, add an export flag field) then you may be stuck do a record by record comparison. If this is the case then maybe you can find a date field (last modified, etc.) that you can use as your export flag. Then you might be able to deal only with the records added from your last transfer to the present date.

In either case you may be stuck dealing with huge recordsets. I have an app that does this, I query the recordsI need from the source database and persist the result set as an XML document, this allows some memory to be released. Them I query the target database and do a record by record comparison to do any updates.

Allow the app to run silently, at night, if the datasets get huge and time becomes a factor. Consider adding command line switches so it can be configured to run automatically.

Thanks and Good Luck!

zemp