MySQL Clone Table

Introduction to MySQL Clone Table

MySQL Clone Table is a feature command in MySQL that allow copying an existing table to a new one either in the same database or for another one. This Clone Table query duplicates the present table in a database with its structure design, indexes, default values, constraints and other characteristics. The process of copying data records of one current table to a fresh one shows a useful example to keep backup of table data to avoid the situation of table failure or security issues. Suppose, if we want to perform any query for testing purpose 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 above code, we will use CREATE TABLE and SELECT statements to copy the table data to newly formed table.

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

Now, again we will implement the CREATE TABLE and SELECT statements to clone the table data records to newly produced table together with 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 that is to be formed is already present in the database or not. For this, it is required to use IF NOT EXISTS clause with the CREATE TABLE query statement.

From this, syntax statement only the main table and its record data are copied. It will not include other objects of 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 its all 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 from a new table by cloning the existing table. Next we will apply the INSERT statement to enter the table data from 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 other 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 the working of it:

• Firstly we will build up an empty table which is based on the existing table definition that comprises of table structure, indexes, column attributes, etc. using CREATE TABLE query statement.
• Then, we will fill up the empty table by inserting the records of existing table with INSERT statement.
• For basic replication, to 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 new 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 the difference that the new table has only table data but no other column attributes or indexes and also other privileges.

Suppose,we want to clone only those data values having EmpSalary greater than 40,000 then, we need to apply 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 Employees_Salary table as follows.

Code:

SELECT * FROM employees_salary;

Output:

Again, if we duplicate Employees table along with its all database objects including indexes, attributes, keys, etc. related with this table to new table, then, we will implement the succeeding 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 as Empdb, so let us copy the Employees table to a new table generating in this database.

Code:

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

View the results from 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 perform the replication of an existing table in the database either within the same database or in another database in the server.
• The copying of 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 to the original existing table.
• This clone table command in MySQL thus proceeds to give data maintenance and security type of services.

Conclusion

The clone table command in MySQL is responsible to take 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 same database or among others.

This is a guide to MySQL Clone Table. Here we discuss the introduction to MySQL Clone Table, how to clone a table with query  examples and advantages respectively. You may also have a look at the following articles to learn more –

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