SQL Cloning or Copying a Table

Cloning or copying a table in SQL is a common operation used for creating backups, testing, or duplicating table structures for analysis. It allows database administrators and developers to work with a copy without affecting the original data.

Example: First, we will create a demo SQL database and table, on which we will use the Cloning command.

Query:

CREATE TABLE EMPLOYEE_COPY AS
SELECT * FROM EMPLOYEE; 

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 ha ve 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 code creates a table named STUDENT.
• The table contains three columns: student_id, name, roll_no
• The column student_id is set as the PRIMARY KEY, which uniquely identifies each student.
• The AUTO_INCREMENT property automatically increases the student_id value for every new record.
• The column roll_no is defined as a UNIQUE KEY, ensuring that no two students have the same roll number.

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:

• The original table STUDENT has a primary key and AUTO_INCREMENT property defined for the student_id column.
• The roll_no column in the original table is defined as a unique key.
• When the table was cloned into STUDENT_COPY, these constraints were not copied.
• The cloned table only contains the same structure and data as the original but does not include the primary key, auto-increment, or unique key definitions.

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 original table STUDENT, the column student_no was set as the primary key.
• In the cloned table STUDENT_COPY, duplicate values appear for the last two entries.
• The AUTO_INCREMENT property also doesn’t work in the cloned table.
• To prevent such issues, we can use the 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:

• In this cloning method, all the table properties are preserved.
• The indexes and the AUTO_INCREMENT property are also inherited from the original table.
• This makes it different from the simple cloning method, where such properties are not copied.
• As a result, the cloned table behaves exactly like the original one in terms of structure and constraints.

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 this cloning method, all the table properties are preserved.
• The indexes and the AUTO_INCREMENT property are also inherited from the original table.
• This makes it different from the simple cloning method, where such properties are not copied.
• As a result, the cloned table behaves exactly like the original one in terms of structure and constraints.