0

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?

  • Most likely it is possible. What's your real question? – mustaccio Apr 7 '17 at 21:20
  • Question is how? – Dave Apr 7 '17 at 22:04
  • Use "id" or any unique field to UPDATE. – Lenin Raj Rajasekaran Apr 7 '17 at 22:10
  • Or, depending on the nature of the data provided (and the data you're storing), import the updated data into a new table or DB, copy over whatever's still relevant of your own data (or delete what's no longer relevant), and then drop or archive the old version. This, of course, assumes the data from the government is not an update, but rather intended as a full refresh. – RDFozz Apr 7 '17 at 22:28
  • "How" is trivial, you write some SQL statements to load data and update your tables. You then put those statements in a text file and schedule it to run every month. – mustaccio Apr 7 '17 at 23:38
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.