Mysql – Updating data when data is from an external source

data synchronizationmigrationMySQL

A government organization provides data in a series of files which can be re-engineered and imported into a database. The files are updated monthly. I have been able to reengineer the database in MySQL and import the data. I've also created additional tables on the database and added my own data. My issue is I'm unsure as to how to handle updates to the database.

How do I import the data every month and sync it with the database from the previous month, which would include the tables that I added?

Best Answer

If the imported data has artificial ids (such as from AUTO_INCREMENT), and your tables are referencing that data via those ids, and each "update" changes those ids, then you need to rethink.

If the imported data has "natural" keys that do not change with each "update", then you are safe.

If the "update" is really a complete set of data, then to reload, do this for each table

DROP TABLE IF EXISTS old;   -- from last month
LOAD DATA .. INTO new ... -- or whatever load mechanism works
RENAME TABLE real TO old, new TO real;

Keep the old tables around until next month -- you may have to revert, or you may want to compare old to real to see, or react to, what changed. See LEFT JOIN to discover what is added/removed. Use a plain JOIN to see what values have changed.

Related Question