Assuming you don't need to eliminate duplicates, you can do this for each table
insert into db1.tablea
select * from db2.tablea ;
Some complications:
- if the tables have id columns, you need to make sure they don't clash, by replacing old ids with new ids
- but, since the ids are the keys that link the tables, you need to make sure that new ids match in each table.
Here's a quick and dirty way to do it:
- Find the highest id in any table in the first database.
- Call this max_key_db1.
- Then update all keys in the second database to be current_value plus max_key_db1.
Note that you'll need to update both primary keys and foreign keys for this to work, e.g.:
update db2.tablea set id = id + max_key_db1, foreign_id = foreign_id + max_key_db1;
update db2.tableb set id = id + max_key_db1, a_id = a_id + max_key_db1;
etc.
Now you have a self-consistent db2 with all keys (primary and foreign) with values that don't exist in db1; in other words, you keys are unique across both databases.
Now you can insert the rows from db2 into db1:
insert into db1.tablea
select * from db2.tablea ;
Note this won't work if the tables inserted into create their own ids using auto-increment or triggers; in this case you'll have to specify the ciolumns explicitly and turn off any auto-generated ids:
insert into db1.tablea( id, foreign_id, col1, ...)
select id, foreign_id, col1 from db2.tablea ;
Alternately, you can leave db2 unaltered, by doing this all in one step for each table:
insert into db1.tablea( id, foreign_id, col3, col4)
select id + max_key_db1, foreign_id + max_key_db1, col3, col4 from db2.tablea ;
Of course, do this all inside a transaction, and don't commit until you're sure you've gotten every table and all are correct. And do this on copies of your databases.
Now, since you used the highest key in db1 regardless of table, likely your ids won't be consecutive, but who cares? Keys are keys. What you will need to do is reset any auto_increment or sequence, for each table, so that teh next auto-generated key is higher than the highest key in that table. How you do that depends on what RDBMS you're using.