MySQL: Copy or Clone Table

In MySQL, we can create a copy/clone of a table using some simple queries which can be useful for archiving the important data that needs to be used.

Now the sole purpose for this copy/clone creation is to fetch table content or needed contents into a separate one in the case of some reporting or reports yet to be presented.

Syntax

CREATE TABLE your_table_name SELECT * from TableName;

This will copy the contents of TableName into the newly created table your_table_name.
Note that when we use this method the id column will not be set as primary key as same as the real table and Autoincrement will also be not available.
So that when we want to add in some info in this table it might get tricky as the column is not auto-incremented and not as the primary key.

So to get all the attributes of the original column and the replica of the table we will use the LIKE clause.

CREATE TABLE your_table_name LIKE TableName;

This will inherit all the table definitions just like that.

When cloning big tables be careful that it may cause delay and cost more resources from the server to finish cloning.

Now let us use a real-time example of copying a subset of records into a table we have just created for this we will be using the shippers' table.

Here is the clone example without using the LIKE clause:

create table shipper_clone select * from shippers;

As we can see we have successfully cloned the shippers' table but the definitions of the table are not exactly just like the table.

Create table shipper_clone using LIKE clause:

create table shipper_clone LIKE shippers;

Now we can see using the LIKE clause we can get the exact definition of the original table and thus we can explicitly add more data into that table.

Copying subset of records into an existing truncated table:

INSERT INTO shipper_clone select * from shippers where shipper_id = 4;

Using the INSERT INTO statement we can send a subset of records from the shipper table where id is 4 to the shipper clone table with ease.