I have a MySQL database, of which all tables originate within MySQL, except one. Once of the tables, and its data, I import through ODBC. When it arrives it does not have a primary key, nor does it have any relationship with the other tables. So, I establish primary key and relationships for this table and the whole database functions perfectly well.
However, I don't much care for deleting an existing table and its relationships, importing the table again and establishing PK, FK links each time I want to update the data in this table.
My question is this, if I import the table and give it a different name, so that it lives alongside the existing live version. And then establish the primary key so that this new table is strctured the same, except for it relationships with the rest of the database - is there a simple query that will compare the newly imported table and old table, and just add records from the new table into the old one that are missing.
Before anybody comments, I know this is all very ugly but I am at the mercy of a terribly poorly implemented ODBC driver from Sage UK. I originally wanted to talk to this table directly through Coldfusion but alas it does not work. So the only way for me to get my hands on the data is to drag it kicking and screaming down a flaky DAO pipe.
One of my main concerns is this: It is not a problem adding new records to the existing table in MySQL as the primary key will ensure there are no duplicates. But what happens if a record (and its key) has been removed from the new table which we then try and copy into the old table. We are trying to remove a record which might have a FK relationship with some other table in the database. Messy.
Any hints would be most appreciated.
PS - The table that gets imported very rarely receives updates, it's a large amount of pretty static data that I do not want to duplicate in another system.