MySQL copy data from one database to another

Let me show you how to copy data from one database to another in MySQL. First of all, you must be sure that you have the rights on both databases, with the user/connection you are currently logged in as. As soon as you have that, its really quite easy:

There are probably different ways to do this, but the one way is using the MySQL INSERT INTO … SELECT statement.

First select the database you are copying the data from:

USE `old_database`;

Now run INSERT INTO on the table and rows of the database you are copying to:

INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)

And finish the query with a selection you wish to insert from the database being copied from:

SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` 
FROM `old_table`

Here is the whole query

USE `old_database`;
INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)
SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` 
FROM `old_table`

You will probably use this when you are re–doing a system and you are building a new database. If you only wish to insert some of the old columns or swap the order this is where this type of query comes in handy.

Leave a Reply