SQL Cloning or Copying a Table

Cloning or copying a table in SQL is a common task in database management. Whether we are creating backups, conducting testing, or needing a duplicate table structure for various purposes, knowing how to effectively clone or copy a table is an essential skill for database administrators and developers.

In this article, we will explain different methods of cloning tables in SQL, provide examples for each, and explain their real-world applications. By the end of this article, we will be able to select the most suitable cloning method based on our needs

What is a Copying or 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 copy can include both the table’s structure (column names, data types, constraints) and optionally its data. The clone table is independent of the original and can be used for testing, backups, or analysis without affecting the original table.

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

• With Data: The clone table includes the structure and rows of the original table.
  
• Without Data: Only the structure of the original table is copied.

Real-Life Scenario Example

Imagine we are developing a library management system. If we want to test new features without risking changes to the production table, we can create a clone of the original table for safe experimentation.

Methods for Cloning Tables in SQL

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

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

Step 1: Create the Original Table

We will use the following table named STUDENT for demonstrating cloning techniques. 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.

Query:

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)
) ;

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');

SELECT * from STUDENT;

Output

Explanation:

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.

1. Simple Cloning

In this method, the clone table creates a copy of the original table’s structure and data, but constraints like primary keys, unique keys, and auto-increment properties are not preserved.

Syntax

CREATE TABLE clone_table SELECT * FROM original_table;

Example:

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

CREATE TABLE STUDENT_COPY AS SELECT * FROM STUDENT;
SELECT * FROM STUDENT_COPY;

Output

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

Explanation:

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

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. It also preserves constraints like primary keys and unique keys.

Syntax

CREATE TABLE clone_table LIKE original_table;

Example:

CREATE TABLE STUDENT_SHALLOW_CLONE LIKE STUDENT;
SELECT * FROM STUDENT_SHALLOW_CLONE;

Output

Insert Data into 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

Explanation:

We 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;

Example:

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. We can add new entries to the deep clone table to confirm the preservation of constraints:

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

SELECT * FROM STUDENT_DEEP_CLONE;

Output

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 us choose the appropriate method for our needs.