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:
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:
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:
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:
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:
Next, it is required to add values to this table. Execute the below statement:
Next, execute the SELECT statement to display the records:
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.
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:
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:
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:
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:
After successful execution, we can verify the table in mystudentdb database using the below command:
In the below output, we can see that the table is successfully copied into one database to another database.