MySQL Copy/Clone/Duplicate Table

MySQL copy or clone table is a feature that allows us to create a duplicate table of an existing table, including the table structure, indexes, constraints, default values, etc. Copying data of an existing table into a new table is very useful in a situation like backing up data in table failure. It is also advantageous when we need to test or perform something without affecting the original table, for example, replicating the production data for testing.

We can copy an existing table to a new table using the CREATE TABLE and SELECT statement, as shown below:

CREATE TABLE new_table_name  

SELECT column1, column2, column3   

FROM existing_table_name;

From the above, first, it creates a new table that indicates in the CREATE TABLE statement. Second, the result set of a SELECT statement defines the structure of a new table. Finally, MySQL fills data getting from the SELECT statement to the newly created table.

If there is a need to copy only partial data from an existing table to a new table, use WHERE clause with the SELECT statement as shown below:

CREATE TABLE new_table_name  

SELECT column1, column2, column3   

FROM existing_table_name  

WHERE condition;

We have to ensure that the table we are going to create should not already exist in our database. The IF NOT EXISTS clause in MySQL allows us to check whether a table exists in the database or not before creating a new table. So, the below statement explains it more clearly:

CREATE TABLE IF NOT EXISTS new_table_name  

SELECT column1, column2, column3   

FROM existing_table_name  

WHERE condition;

It is to be noted that this statement only copies the table and its data. It doesn’t copy all dependent objects of the table, such as indexes, triggers, primary key constraints, foreign key constraints, etc. So the command of copying data along with its dependent objects from an existing to the new table can be written as the following statements:

CREATE TABLE IF NOT EXISTS new_table_name LIKE existing_table_name;  

  

INSERT new_table_name SELECT * FROM existing_table_name;

In the above, we can see that we need to execute two statements for copying data along with structure and constraints. The first command creates a new table new_table_name that duplicates the existing_table_name, and the second command adds data from the existing table to the new_table_name.

MySQL Copy/Clone Table Example

Let us demonstrate how we can create a duplicate table with the help of an example. First, we are going to create a table named “original_table” using the below statement:

CREATE TABLE original_table (  

    Id int PRIMARY KEY NOT NULL,   

    Name varchar(45) NOT NULL,   

    Product varchar(45) DEFAULT NULL,   

    Country varchar(25) DEFAULT NULL,   

    Year int NOT NULL  

);

Next, it is required to add values to this table. Execute the below statement:

INSERT INTO original_table( Id, Name, Product, Country, Year)   

VALUES (1, 'Stephen', 'Computer', 'USA', 2015),   

(2, 'Joseph', 'Laptop', 'India', 2016),   

(3, 'John', 'TV', 'USA', 2016),  

(4, 'Donald', 'Laptop', 'England', 2015),  

(5, 'Joseph', 'Mobile', 'India', 2015),  

(6, 'Peter', 'Mouse', 'England', 2016);

Next, execute the SELECT statement to display the records:

mysql> SELECT * FROM original_table;  

We will get the output, as shown below:

Now, execute the following statement that copies data from the existing table “original_table” to a new table named “duplicate_table” in the selected database.

CREATE TABLE IF NOT EXISTS duplicate_table   

SELECT * FROM original_table

After the successful execution, we can verify the table data using the SELECT statement. See the below output:

Sometimes there is a need to copy only partial data from an existing table to a new table. In that case, we can use the WHERE clause with the SELECT statement as follows:

CREATE TABLE IF NOT EXISTS duplicate_table   

SELECT * FROM original_table WHERE Year = '2016';

This statement creates a duplicate table that contains data for the year 2016 only. We can verify the table using a SELECT statement, as shown below:

Suppose there a need to copy an existing table along with all dependent objects associated with the table, execute the two statements that are given below:

mysql> CREATE TABLE duplicate_table LIKE original_table;  

AND,  

mysql> INSERT duplicate_table SELECT * FROM original_table;

Let us see how we can copy a table to a different database through an example.

Suppose there is a situation to copy a table from a different database. In that case, we need to execute the below statements:

CREATE TABLE destination_db.new_table_name   

LIKE source_db.existing_table_name;  

  

INSERT destination_db.new_table_name   

SELECT * FROM source_db.existing_table_name;

In the above, the first command creates a new table in the selected(destination) database by cloning the existing table from the source database. The second command copies data from the existing table to the new table in the selected database.

The following demonstration explains it more clearly.

Suppose we have two databases named “mysqltestdb” and “mystudentdb” on the MySQL Server. The mytestdb database contains a table named “original_table” that have the following data:

Now, we are going to copy this table into another database named mystudentdb using the following statement:

CREATE TABLE mystudentdb.duplicate_table   

LIKE mysqltestdb.original_table;  

  

INSERT mystudentdb.duplicate_table   

SELECT * FROM mysqltestdb.original_table;

  

After successful execution, we can verify the table in mystudentdb database using the below command:

mysql> SELECT * FROM mystudentdb.duplicate_table;  

In the below output, we can see that the table is successfully copied into one database to another database.