Home / Tricky Select Queries / Question No: 58

I want to move my table alongwith its data from one to another MySQL database. Please help to explain, how to move table from one to another MySQL database including its data?

In MySQL you can do this by different methods. By first of the following examples, you can also move multi tables from one to another database in one go. Take an example:

RENAME TABLE current_db.table1 TO other_db.table1,
         current_db.table2 TO other_db.table2,
       current_db.table3 TO other_db.table3;

Another method can be like this: Create same table in your second database and run the the query:

INSERT INTO second_db.table_1 SELECT * FROM first_db.table_name;

Finally, drop the unwanted table from first database like below:

DROP TABLE first_db.table_name;