Clone Table in SQL

What is Cloning Table in SQL and When do We Use it?

Cloning is an operation in SQL that allows us to make a copy of an existing table, thereby creating a new table that can be exactly a duplicate copy along with the data from the original table or might just have the structure of the original table without any data. The new table thus created is called a clone table in SQL.

Let us assume a scenario where you are developing an app that involves some databases, and there is a situation where you need a table that is similar to a pre-existing table. This might be due to the fact that some new feature is being implemented or some tests are to be done on the existing table. So, by any means, you would not like the original table to be tampered with. In such a situation we make a clone table in SQL, thereby saving our time and effort of creating an entirely new table and entering all the same data once again.

How to Clone Tables in SQL?

There are mainly three methods to create a clone table in SQL, namely:

• Simple cloning
• Shallow cloning
• Deep cloning

Let us CREATE a table with the name EMPLOYEE to which we shall be applying all three methods of cloning.

The above MySQL code creates a table named EMPLOYEE which has three columns Emp_Id, Name, and Dept. The Emp_Id is set as the PRIMARY KEY and has an attribute of AUTO_INCREMENT. Then it adds three entries to the table using the INSERT operation. Finally, we output the table.

EMPLOYEE table:

Now, let us have a look at each of the three different cloning methods in SQL and apply it to the table we just created for a better understanding.

Simple cloning

As the name suggests, this is the simplest method of creating a clone table in SQL. In this method, the clone table inherits only the basic structure, the NULL settings, and the default values from the original table. It does NOT inherit the indices and AUTO_INCREMENT* definitions.

Syntax:

Let us now take an example where we make a clone of the EMPLOYEE table using simple cloning and name it EMPLOYEEcopy then let's INSERT two more items in the EMPLOYEEcopy table and see if in the Emp_Id in the clone supports the AUTO_INCREMENT definition or not.

Syntax

In the above MySQL code, we make a copy of the table EMPLOYEE and name it EMPLOYEEcopy and then INSERT two new entries to it. Finally, we print the clone table.

EMPLOYEEcopy table:

As you can see, in the original table, we had set the Emp_Id as the PRIMARY KEY, but now in the simple clone table, there are duplicate values for the last two entries. Moreover, the AUTO_INCREMENT definition becomes invalid here. To avoid this, we use the Shallow cloning technique.

Shallow cloning

Shallow cloning is the technique in which the clone table gets the exact same structure as the original table, but it DOES NOT get the data from the original table.

So, we basically get an empty table with the exact structure like the original table, including the attributes like the indices and the AUTO_INCREMENT.

This type of cloning is done if we want only the original table's structure and all the column attributes.

Syntax:

Let us apply this method to the original EMPLOYEE table and make a clone named EMPLOYEEcopy and let us print the clone and see what we have.

MySQL code to make a clone and print it:

The above MySQL code first creates a clone table named EMPLOYEEcopy which is “LIKE” the original EMPLOYEE table in every aspect, and then we print the clone table.

Output:

And that’s what we end up with! Our code does not output anything, which means we have AN EMPTY TABLE!

But, this empty table has the exact structure of the original EMPLOYEE table including all the properties like indices and AUTO_INCREMENT definition.

Let us add two new entries and have a look!

MySQL code to add two new entries:

In the above MySQL code, we INSERT two new entries to validate the structure and the functioning of indices and AUTO_INCREMENT attribute in the “Shallow” clone. Finally, we print the clone table.

EMPLOYEEcopy table after adding two new entries:

As we can see, the indices and AUTO_INCREMENT definitions are maintained in the clone but all the data that were there in the original table are not present in the clone.

Deep cloning

This method is the more widely used one for creating clone tables in SQL, as in here, every property of the original table is maintained like the indices and the AUTO_INCREMENT. Alongside this, we get the data copied to the clone table from the original table.

Precisely, Deep cloning is Shallow cloning along with copying of the data from the original table.

Syntax:

Let us apply the Deep cloning method on our original EMPLOYEE table and make a clone named EMPLOYEEcopy.

MySQL code to make a clone and print it:

In the above code, we CREATE an empty clone (just structure, without any data) of the table EMPLOYEE and name it EMPLOYEEcopy, and then we INSERT the data from the original EMPLOYEE table to the EMPLOYEEcopy clone table. Finally, we print the clone table.

EMPLOYEEcopy table:

The output is exactly the same as the original EMPLOYEE table.

Now let us add two new entries to validate the functioning of the indices and the AUTO_INCREMENT definition.

MySQL code to insert new values to the clone:

In the above code, we INSERT two new entries to the EMPLOYEEcopy table to validate the functioning of the indices and the AUTO_INCREMENT definition. Finally, we print the clone table.

EMPLOYEEcopy table after adding the two new entries:

As we can see, in this method the data from the original table is maintained. Alongside we have the column attributes like indices and AUTO_INCREMENT maintained as well.