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?

Improve this question
5
  • Most likely it is possible. What's your real question?
    – mustaccio
    Commented Apr 7, 2017 at 21:20
  • Question is how?
    – Dave
    Commented Apr 7, 2017 at 22:04
  • Use "id" or any unique field to UPDATE.
    – Raj
    Commented Apr 7, 2017 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
    Commented Apr 7, 2017 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
    Commented Apr 7, 2017 at 23:38

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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