MySQL Copy Table From One Database To Another


So you are looking at transferring the data of on MySQL Table into another MySQL Table. Migrating data from database to another database table can be surprisingly easier than you would think.

MySQL Copy Table From One Database To Another

The following query will copy all rows from table_one into table_two. In order for this to work correctly, the number of columns must match. It doesn’t matter if the names of the columns are different, so long as the column count is the same.

INSERT INTO `table_two` SELECT * FROM `table_one`;

MySQL Copy Table With Different Number Of Columns

You can also copy data where the column count is different between the two tables. In order to achieve this, your SELECT will have to return the right number of columns as INSERT statement is expecting.
The exciting part of this is that you can filter which results get transferred by using normal MySQL “SELECT WHERE” syntax as you can see in the below example. In the example, we only transfer the records from table one that are LIKE ‘some value’ and limit the transfer to 20 results.

INSERT INTO `table_two` (`column_A`, `column_B`) SELECT `column_C`, `column_D` FROM `table_one` WHERE `column_name` LIKE '%some value%' LIMIT 20;

Important Note

In the above query, you will notice I select column_C and column_D but I insert it into column_A and column_B. This will work fine even thought the column names don’t match. As long as the column count is the same you will be fine. Assuming that you don’t have foreign key conflict or are missing required columns.

Simple as that.