SQL - Clone Tables😉

Sometimes, in some situations, we need the exact copy structure of the table or the exact copy structure of a table with data.

In SQL, Clone means when you create an exact copy of your original table or only copy the exact structure of your table.

When is an SQL Clone required?

  1. When we perform some tests, there is a chance to change the data, so without affecting the original table, we create a copy of the table and perform all tests on that table.
  2. When we want to make a backup of the original table. So we create a backup table with a clone.

In such a circumstance, we make a clone table in SQL. In this manner, we spare our time and exertion of making a totally new table and entering all the same information once.

There are three kinds of SQL clones.

1. Simple cloning

This is the most simple way to clone a table. Simple cloning simply means copying data from the original table without inheriting any column attributes or indexes.

Syntax

CREATE TABLE <new_table> SELECT * FROM <original_table>;

2. Shallow cloning

Shallow cloning is generally employed to form a duplicate of an existing table's information structure and column traits without the information being replicated. This will, as it were, make a purge table based on the structure of the first table.

This type of cloning is done when you only need the structure and all column attributes of the original table.

Syntax

CREATE TABLE <new_table> LIKE <original_table>;

3. Deep cloning

Deep cloning is similar to shallow cloning in that it copies data from the original table. As a result, deep cloning copies both the data and the structure of the original table.

This strategy is the more broadly utilized one for making clone tables in SQL, as here each property of the first table is kept up just like the records and the auto_increment. From the original table, we get the information replicated to the clone table.

Syntax

CREATE TABLE <new_table> LIKE <original_table>;
INSERT INTO <new_table> SELECT * FROM <original_table>;

Summary

Cloning means copying data from one table to another.

  • Simple Cloning - Simply copy data from the original table without any column attributes and index.
  • Shallow Cloning - Copy only the structure from the original table with all column attributes and index. However, without table data
  • Deep Cloning - Same as shallow cloning but also clone table data