How to append a mysql table to another table in a different database

Question

I would like to grab a table from one database and append this data to a table in another database. However, they have similar numbers (including the id) which need to be updated before they can be copied over. Is there a function available that could do this automatically? Or do I need to write a script in between?

So far I've got:

#!/bin/sh
mysqldump -uuser1 -ppw1 database1 table1 > /home/user/public_html/database1.sql --skip-add-drop-table --skip-create-options
mysql -uuser2 -ppw2 database2 < /home/user/public_html/database1.sql
rm /home/user/public_html/database1.sql

Solution 1

You could select from one table and insert it into another. The results will be "appended" to the original data.

insert into new_table (id, name) select old_id, old_name from old_table;

To append a table from one database to a table from an other database

insert into new_database.new_table (id, name) select old_id, old_name from old_database.old_table; 

Solution 2

Sounds like something that would be a lot safer to do via script, which seems simple enough - just grab the data from the first DB and perform batch inserts into the other, letting mysql handle the ids itself. This should take about 10-30 LOC in any descent scripting language, and gives you more control over the outcome.