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.
This method can be utilized to create a temporary table for backup before doing a major change on the actual table so that in case we execute the wrong query and delete/change something unintentionally we can revert it from the temporary backup table easily.
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 a 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;Another way to create a table directly using the below query command.
create table new_table select * from source_table;Example:
create table mytechmint_post_temp select * from mytechmint_post;Copy a Part of Data
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 the ‘source_table’ table can be of different table structures. Still, it copies the data.
If you know of any new statements in MySQL that copy data from one table to the other table, please let us know in the comments.
