MySQL Clone Table

Updated May 26, 2023

Introduction to MySQL Clone Table

MySQL Clone Table is a feature command that allows copying an existing table to a new one in the same database or for another. This Clone Table query duplicates the present table in a database with its structure design, indexes, default values, constraints, and other characteristics. Copying data records from one current table to a fresh one shows a valuable example of keeping a backup of table data to avoid the situation of table failure or security issues. If we want to perform any query for testing purposes but not hampering the original table, then this clone table is beneficial to create a replica of data.

Syntax of MySQL Clone Table

For defining a MySQL Clone Table, we need to apply the succeeding basic syntax structure for writing a query statement in MySQL:

CREATE TABLE NewTable
SELECT ColumnName1, ColumnName2,…
FROM ExistingTable;

For this code above, we will use CREATE TABLE and SELECT statements to copy the data to the newly formed table.

In this syntax, a new table is created initially with the specified name indicated in the statement CREATE TABLE. The result set produced by the SELECT statement defines the structure of this new table. Then, MySQL occupies the new table with the data output from the SELECT query statement.

Now, again we will implement the CREATE TABLE and SELECT statements to clone the table data records to the newly produced table together with the WHERE clause:

CREATE TABLE NewTable
SELECT ColumnName1, ColumnName2,…
FROM ExistingTable WHERE Conditional_Expressions;

Before we create any new table, it is essential to validate whether the table to be formed is already present in the database. For this, the IF NOT EXISTS clause with the CREATE TABLE query statement is required.

Only the main table and its record data are copied from this syntax statement. It will not include other objects of the database like PRIMARY KEY CONSTRAINT, INDEXES, UNIQUE KEY, FOREIGN KEY CONSTRAINTS, TRIGGERS, and so on that is linked with the existing table.

So, we will do this by using the subsequent statement as follows, where the present table, along with all its dependent database objects, will be duplicated:

CREATE TABLE IF NOT EXISTS NewTable LIKE ExistingTable;
INSERT NewTable
SELECT * FROM ExistingTable;

Here, we are required to run two query statements, as shown above. From CREATE TABLE statement, firstly, we will form a new table by cloning the existing table. Next, we will apply the INSERT statement to enter the table data from the existing one into the NewTable.

But, below is the complete query syntax to form a new table by replicating an existing table data to it in another database:

CREATE TABLE Target_Db.NewTableLIKE Source_Db.ExistingTable;
INSERT Target_Db.NewTable
SELECT * FROM Source_Db.ExisitngTable;

Here, Target_Db represents the destination database name, and Source_Db denotes from where we want to copy.

How to Clone a Table in MySQL?

For cloning a table in MySQL, the following steps describe its working of it:

• Firstly we will build up an empty table based on the existing table definition comprising table structure, indexes, column attributes, etc., using CREATE TABLE query statement.
• Then, we will fill the empty table by inserting the existing table records with an INSERT statement.
• For basic replication, copy just the table structure and data of the table of source and not the other database objects using CREATE TABLE and SELECT query as explained in the syntax code above.

Examples of MySQL Clone Table

Given below are the examples mentioned :

Example #1

MySQL Clone Table Example within the same database.

Suppose we have a table named Employees that exists already in the database. Then, we will copy the data from this table to a new table in the same database.

Code:

select * from employees;

Output:

Code:

CREATE TABLE IF NOT EXISTS Employees_Copy
SELECT * FROM Employees;

Here, we have copied the Employees named table data to the newly formed table as Employees_Copy. After executing the code, we need to validate the query in the table Employees_Copy as follows.

Code:

SELECT * FROM employees_Copy;

Output:

On comparing the original and new table, we will see that the new table has only table data, no other column attributes or indexes, and other privileges.

Suppose we want to clone only those data values having EmpSalary greater than 40,000. Then, we must apply the WHERE clause to the SELECT query using the following statement.

Code:

CREATE TABLE IF NOT EXISTS Employees_Salary
SELECT * FROM Employees WHERE EmpSalary> 40000 ;

Now, let us view the contents from the Employees_Salary table as follows.

Code:

SELECT * FROM employees_salary;

Output:

Again, suppose we duplicate the Employees table and all its database objects, including indexes, attributes, keys, etc., related to this table to a new table. In that case, we will implement the following query statement.

Code:

CREATE TABLE Employees_Dup LIKE Employees;
INSERT Employees_Dup
SELECT * FROM Employees;

Let us view the result of Employees_Dup like this.

Code:

SELECT * FROM employees_dup;

Output:

Example #2

MySQL Clone Table Example in the other database.

We have another database named Empdb, so let us copy the Employees table to a new table generated in this database.

Code:

CREATE TABLE Empdb.Employees_Data LIKE Personsdb.Employees;
INSERT Empdb.Employees_Data
SELECT * FROM Personsdb.Employees;

View the results from the Empdb database.

Code:

SELECT * FROM Empdb.Employees_Data;

Output:

Advantages of MySQL Clone Table

Given below are the advantages mentioned:

• This MySQL Clone Table shows several techniques to replicate an existing table in the database, either within the same or in another server database.
• Copying a table is significant in taking exact data backups of the database to resist any data failure issues.
• Also, for testing purposes, to clone the production data records of a table available in the server without causing any effect on the existing table.
• This clone table command in MySQL thus gives data maintenance and security type of services.

Conclusion

The clone table command in MySQL is responsible for taking a future backup of data records to survive in any failure condition. This also helps to provide the replica and production data to test without involving the main table so that we can check the functionality and data security for the tables within the same database or among others.

We hope that this EDUCBA information on “MySQL Clone Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL Admin Tool
2. UNIQUE INDEX in MySQL
3. MySQL REGEXP
4. MySQL GROUP_CONCAT()