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));

INSERT INTO B.foo (id, col11, col3) SELECT id, col1, col3 FROM A.foo — dwalldorf
– dwalldorf, Commented Nov 1, 2011 at 9:55

Gryphius · Accepted Answer · 2011-11-01 10:06:54Z

12

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;

edited Nov 1, 2011 at 10:06

answered Nov 1, 2011 at 9:55

Gryphius

79.8k6 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

priya Over a year ago

Is there a way to pass the database names as arguments to the sql script which will be executed on database 'B'

Gryphius Over a year ago

if you look at the sql statements above you can see that the database name is already included, you don't need to select a database in your sql script. eg. insert into <databasename>.<tablename> ... works even if you have not selected "<databasename>" before

priya Over a year ago

No, is there a way to pass the source database as an argument (e.g. for 'A')

Konerak Over a year ago

@priya: you could dynamically generate the statements in a language of your choice (php, ruby, Perl, Java...), or using MySQL's PREPARE FROM.

Gryphius Over a year ago

just found a possible way.. I added it as a new answer, it's a bit too long for a comment

|

PatrikAkerstrand · Accepted Answer · 2011-11-01 09:56:54Z

1

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;

answered Nov 1, 2011 at 9:56

PatrikAkerstrand

45.8k12 gold badges82 silver badges94 bronze badges

Comments

Brendan Bullen · Accepted Answer · 2011-11-01 09:58:26Z

1

IF you know the columns that have been removed you can do a straight insert of query results:

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

answered Nov 1, 2011 at 9:58

Brendan Bullen

11.8k1 gold badge33 silver badges40 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:02:00Z

1

another solution that includes your 2nd question to set the source database name dynamically

create your script 'bla.sql':

SET @q = CONCAT('INSERT INTO B.foo1 (id, col11, col3) 
  SELECT id,col1,col3 FROM ',@sourcedb,'.foo');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(add additional statements as needed)

then run from console:

mysql -e "SET @sourcedb:='A' ; source bla.sql;" B -u root

source for this solution: variable database name and koneraks comment ;-)

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Nov 1, 2011 at 10:37

Gryphius

79.8k6 gold badges52 silver badges56 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related