SQL Cloning or Copying a Table

Cloning or copying a table in SQL is a common task encountered in database management. Whether you’re creating backups, performing testing, or need to duplicate a table structure for various purposes, knowing how to effectively clone or copy a table is essential. In this article, we’ll explore different methods and good practices for achieving this in SQL.

Cloning tables is an operation in SQL that allows us to make a copy of an existing table. The clone table can be just the structure of the original table without any data or an exact copy of the original table.

Note: This Article will be following the MySQL Syntax but cloning operations can be done in other Relational Database Management systems (RDBMS) such as Postgre SQL, and Microsoft SQL Server, and syntax may follow as per their document.

What is Cloning Table in SQL

SQL Cloning is an operation that means making a copy of a table. It’s like taking a photocopy of a document. This lets you create a new table that’s exactly like the original one (current table), including both the structure (like column names and types) and the data inside it. Or, We can just copy the structure without any data. The new table created this way is called a clone table. It’s a way to duplicate a table quickly and easily.

Real Life Scenario Example

Imagine you’re developing an app for a bookstore or a library management system where you have a database to store information about books. Now, let’s say you’re introducing a new feature or conducting tests on the existing book table. Moreover, you want to avoid any risk of changes or altering the original book table. In this scenario, you can create a clone table in SQL.

Why should we use the SQL Cloning

Cloning a table in SQL means making a duplicate copy of an existing table. It’s like making a backup so that you can experiment or work with the data without affecting the original table. This saves you the time and effort of creating a completely new table and re-entering all the same data.

Let’s see the example of SQL Cloning

• To create a clone table with the same structure and data as the original ‘books‘ table

CREATE TABLE books_clone AS
SELECT * FROM books;

• To create a clone table with the same structure as the original ‘books‘ table but without any data

CREATE TABLE books_clone_structure LIKE books;

Different Methods of SQL Table Clone

There are three different methods to create a clone table in SQL:

1. Simple Cloning
   
2. Deep Cloning
3. Shallow Cloning

Let us CREATE a table with the name STUDENT to apply all three methods of SQL cloning.

CREATE TABLE STUDENT(
     student_id int NOT NULL AUTO_INCREMENT,
     name varchar(255) NOT NULL,
     roll_no varchar(255) NOT NULL UNIQUE,
     PRIMARY KEY (student_id)
) ;

Let us INSERT the data into our newly Created table “STUDENT”

INSERT INTO STUDENT(student_id, name, roll_no)
VALUES (1, 'Ritwik Dalmia', 'S100');
INSERT INTO STUDENT(student_id, name, roll_no)
VALUES (2, 'Rohan Singh', 'S200');
INSERT INTO STUDENT(student_id, name, roll_no)
VALUES (3, 'Mohan Singh', 'S300');

Let us fetch the data we have inserted into the “STUDENT” table

SELECT * from STUDENT;

The above MySQL code is used to create a table called “STUDENT” which has three columns student_id, name, and roll_no where student_id is defined as PRIMARY KEY with AUTO_INCREMENT command and roll_no is defined as UNIQUE KEY.

After creating the table we used INSERT OPERATION to insert the three entries in the “STUDENT” Table. Finally, we have used SELECT OPERATION to fetch the data to see the output.

Output:

STUDENT TABLE

1. Simple Cloning

In this method, the clone table only inherits the basic structure, default values, and NULL settings but it does not inherit the indices and AUTO_INCREMENT.

Syntax:

CREATE TABLE clone_table SELECT * FROM original_table;

Let us see the example to understand how simple cloning syntax works

CREATE TABLE STUDENT_COPY  SELECT * FROM STUDENT;

Let’s see whether the cloning of the STUDENT_COPY is successfully executed or not;

SELECT * FROM STUDENT_COPY;

Output:

Cloning of the table

Let’s see the property of both the tables STUDENT and STUDENT_COPY respectively

Property inherits while cloning the sql tables

As we can see that in original table “STUDENT”, we have primary Key and auto_increment command for student_id and unique key for roll_no but in “STUDENT_COPY” clone table we do not have the primary key, auto_increment, and unique key respectively.

Drawback Of Simple Cloning

Simple cloning in SQL lacks preservation of unique constraints and auto-increment properties, potentially leading to data integrity issues. Mitigation involves manually reapplying constraints and resetting auto-increment settings. Consider alternative cloning methods for better results.

Output:

Drawback of simple cloning

Explanation: In the above output, you can see that in the original table “STUDENT” we had set the student_no as a primary key but now in the simple clone table “STUDENT_COPY” values, there are duplicate value for the last two entries and Auto_increment command also becomes invalid here. To avoid this, we will be using Shallow cloning technique.

2. Shallow Cloning

Shallow cloning is the method in which the clone table gets the same structure as the original table but it does not inherits or copy the data from the original table.

In other words, we will have the empty table including indices such as primary key, unique key, and auto_increment.

Syntax:

CREATE TABLE clone_table  LIKE original_table;

let’s, see the properties of the shallow clone table “STUDENT_SHALLOW_CLONE” using describe command.

Output:

Shallow clone of student table (Original Table)

SELECT * FROM STUDENT_SHALLOW_CLONE;

Let us INSERT the data into our newly Created table “STUDENT_SHALLOW_CLONE”

INSERT INTO STUDENT_SHALLOW_CLONE(name, roll_no)
VALUES ('Ritwik Dalmia', 'S100');
INSERT INTO STUDENT_SHALLOW_CLONE(name, roll_no)
VALUES ( 'Rohan Singh', 'S200');
INSERT INTO STUDENT_SHALLOW_CLONE( name, roll_no)
VALUES ( 'Mohan Singh', 'S300');

Output:

working of the shallow clone method

Explanation: You can able to see that all the properties such as indices and auto_increment command are inherited in this method as compare to simple cloning method.

3. Deep Cloning

This method is widely used for creating the clone tables in SQL as it inherits all the properties of original table including indices such as primary key, unique, and auto_increment as well as inherits the existing data from the original table.

Syntax:

CREATE TABLE clone_table LIKE original_table;
INSERT INTO clone_table SELECT * FROM original_table;

Let us create the table using deep clone method

CREATE TABLE STUDENT_DEEP_CLONE LIKE STUDENT;
INSERT INTO STUDENT_DEEP_CLONE SELECT * FROM STUDENT;
SELECT * FROM STUDENT_DEEP_CLONE;

Output:

The output of the “STUDENT_DEEP_CLONE” is exactly the same as the “STUDENT” table.

INSERT INTO STUDENT_DEEP_CLONE (name,roll_no) 
VALUES ('mohini roy', 'S400');
INSERT INTO STUDENT_DEEP_CLONE (name,roll_no) 
VALUES ('surbhi roy', 'S500');

Output:

New entries in deep clone table

Explanation: In the above output, we performed the INSERT operation for two entries to the “STUDENT_DEEP_CLONE” table to validate the functionality or properties of the indices and the AUTO_INCREMENT function. Finally, we clone the sql table.

Conclusion

Cloning is a useful method in SQL for creating a copy of an existing table. There are three main methods of cloning a table: simple cloning, shallow cloning, and deep cloning. Simple cloning only copies the basic structure of the table, while shallow cloning copies the structure without any data. Deep cloning, on the other hand, copies all properties of the original table, including indices like primary key, unique, and auto-increment, as well as any existing data. Each cloning method has its uses and benefits depending on the situation. Knowing the differences between these cloning methods can help you choose the appropriate method for your needs.