The scenario is, I need to copy subsets of 32 tables (several have over a million rows) from a remote vendor oracle server I have read only access to to our local SQL db for processing/reporting on as close to a “live” timeline as possible. Most of the tables have a “Last Updated” timestamp but some of the larger ones do not.
Full Drop and reload takes ~50 minutes
I tried doing a scheduled stored procedure merge between initial full load (target) and a fresh temp table with only records last updated since max last update of full load (where available) with no deletes and that takes ~7 minutes but the no deletes is resulting in inconsistent row counts when comparing source vs local.
I was planning to remedy this with nightly full reloads off hours, but there is still the potential for duplicate data.
Is there a better, more reliable way to do this?
Damon Palyka is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.