How to move data between 2 tables / with different columns in different databases

Question

We have the following scenario where foo has been renamed as foo1.

foo.col1 has been renamed as foo1.col11.

foo.col2 has been removed

In fact these used to be similar tables and I would like to copy data from A to B for these tables. How would I go about doing a simple migration given that the table/column names have undergone a change.

Database 'A'
create table foo {id pk, col1 varchar(255), col2 tinyint(1), col3 datetime);
create table foo_bar1 (id pk, foo_id fk, col4 datetime, col5 varchar(255));

Database 'B'
create table foo1 {id pk, col11 varchar(255), col3 datetime);
create table foo1_bar1 (id pk, foo1_id fk, col4 datetime, col5 varchar(255));

Answer 1

you should be able to do:

INSERT INTO B.foo1 (id, col11, col3) 
 SELECT id,col1,col3 FROM A.foo;

INSERT INTO B.foo1_bar1 (id, foo1_id, col4, col5) 
 SELECT id,foo_id,col4,col5 FROM A.foo_bar1;

Answer 2

BEGIN;
INSERT INTO B.foo1 (id, col11, col3) 
   SELECT id, col1, col3 
   FROM A.foo;
INSERT INTO B.foo1_bar1 (id, foo1_id, col4, col5) 
   SELECT id, foo_id, col4, col5 
   FROM A.foo_bar1;
COMMIT;