Export table from mysql, import into a table with a different name

Question

We are releasing a new product based on an existing product of ours, for that we have created a new mysql database with a different scheme.

Now we need some of the tables from our previous database in the new db, but for design purposes, names of the tables are different in the new db (but all the fields are identical). If the names were the same, I could export the table from the previous db and import it into new one.

How can I do this now the names of the tables are different?

open the dump and replace the table names. – Jens Mar 17 '15 at 08:59 — Jens, Mar 17 '15 at 08:59

score 2 · Answer 1 · edited May 23 '17 at 12:23

You can export and import these tables and then rename the table with this:

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

See this link for more details: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

Then you can rename the columns like this:

ALTER TABLE xyz CHANGE manufacurerid manufacturerid INT

As is mentioned in this StackOverflow: Error renaming a column in MySQL

Jay · Answer 2 · 2020-07-13T07:32:34.283

1

In MySQL,

 create table new_table_name(select * from old_table_name);

which will copy all the data from old_table to a new table.

edited Jul 13 '20 at 07:32

answered Mar 15 '16 at 10:41

Jay

1,575
1
17
22

However this will not copy indices. – jarib Jul 12 '21 at 13:04

score 0 · Answer 3 · answered Mar 17 '15 at 09:05

I don't think you can do it directly however if you do not want to change the dump file and change the table names you can follow the following steps

Tables which needs to be imported into the new db, create a copy of the same table with the new name in old db and copy the data in it.
Export each table and then import them to the new DB.

Here is an example

mysql> select * from table1 ;
+------+------+
| t1id | name |
+------+------+
|    1 | aa   |
|    2 | cc   |
|    3 | dd   |
|    4 | ee   |
|    5 | ff   |
|    6 | bb   |
|    7 | gg   |
+------+------+
7 rows in set (0.00 sec)

mysql> create table table1_copy like table1 ;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into table1_copy select * from table1 ;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from table1_copy ;
+------+------+
| t1id | name |
+------+------+
|    1 | aa   |
|    2 | cc   |
|    3 | dd   |
|    4 | ee   |
|    5 | ff   |
|    6 | bb   |
|    7 | gg   |
+------+------+
7 rows in set (0.00 sec)

Then you can export each table as

mysqldump -u username -p dbname table1_copy > table1_copy.sql

Import the new table as mysql -u username -p < table1_copy.sql

And finally delete the temp tables created on the old db.

Or export eveything, and rename the tables.

Export table from mysql, import into a table with a different name

3 Answers3