Copy data from one table to another table in MySQL

There could be a situation where you need to copy data from one table to another table. It’s a lot easier than you think to copy data from one table to another table of similar structure.

Copy data between similarly structured tables

copy complete data

If you want to copy existing rows of source tables into a new destination table, first you need to create a destination table like source table.

create table destination_table like source_table

After creating a destination table like the source table. Now copy all the data from the source table to the destination table.

insert into destination_table select * from source_table

copy a part of data

Where clause in the source table statement needs to be included to copy data from the source table to the destination table. The following is the statement used in MySQL to copy a part of the data from the source table to the destination table.

insert into destination_table select * from source_table where city='New York'

Copy data between different structured tables

There is a MySQL statement where you can copy data from one table to another table of different table structures.

Copy based on selecting columns

This statement in MySQL can copy a few columns from the source table to the destination table.

insert into destination_table_new (address,city,pincode) select address,city,pincode from source_table;

In the above statement, both the ‘destination_table_new’ table and ‘source_table’ table can be of different table structures. Still, it copies the data.

If you know any new statements in MySQL that copies data from one table to the other table, please let us know in the comments.