SQL Cloning or Copying a Table

Last Updated : 14 Apr, 2026

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.

Screenshot-2026-01-19-152855

Query: 

CREATE TABLE EMPLOYEE_COPY AS
SELECT * FROM EMPLOYEE;

Output:

Screenshot-2026-01-19-153006

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.

Screenshot-2026-01-19-153308
  • Table STUDENT has three columns: student_id, name, roll_no
  • student_id is PRIMARY KEY and AUTO_INCREMENT.
  • roll_no is UNIQUE to prevent duplicates
  • Each student has a unique ID and 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.

Query:

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

Output:

Screenshot-2026-01-19-153401

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

Property
STUDENT & STUDENT_COPY TABLE
  • Original STUDENT table: student_id is PRIMARY KEY and AUTO_INCREMENT
  • roll_no is UNIQUE in the original table
  • Cloned table STUDENT_COPY does not retain constraints
  • It only copies structure and data, without primary key, auto-increment, or unique key

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.

Query:

INSERT INTO STUDENT_COPY VALUES
(0, 'Noah Anderson', 'S400'),
(0, 'Emma Johnson', 'S500');

Output:

Screenshot-2026-01-19-155803
  • In the original table STUDENT, the column student_id was set as the primary key.
  • In STUDENT_COPY, the last two entries have duplicate values.
  • AUTO_INCREMENT doesn’t work in the cloned table.
  • Using Shallow Cloning can prevent these issues.

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;

Query:

CREATE TABLE STUDENT_SHALLOW_CLONE LIKE STUDENT;
SELECT * FROM STUDENT_SHALLOW_CLONE;

Output:

Screenshot-2026-01-19-160048

Insert Data into Shallow Clone:

INSERT INTO STUDENT_SHALLOW_CLONE (name, roll_no)
VALUES ('Ethan Miller', 'S100');

INSERT INTO STUDENT_SHALLOW_CLONE (name, roll_no)
VALUES ('Olivia Brown', 'S200');

INSERT INTO STUDENT_SHALLOW_CLONE (name, roll_no)
VALUES ('Liam Wilson', 'S300');

Output:

Screenshot-2026-01-19-160048
  • This cloning method copies the table structure and may include indexes and AUTO_INCREMENT
  • It does not copy all properties (like triggers, foreign keys, permissions, metadata)
  • The cloned table does not work exactly like the original

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;

Query:

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

Output:

Screenshot-2026-01-19-160048

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 ('Emma Johnson', 'S400');

INSERT INTO STUDENT_DEEP_CLONE (name, roll_no)
VALUES ('Sophia Davis', 'S500');

SELECT * FROM STUDENT_DEEP_CLONE;

Output:

Screenshot-2026-01-19-161217
  • This cloning method preserves all table properties.
  • Indexes and AUTO_INCREMENT are copied from the original table.
  • The cloned table works like the original with the same structure and constraints.
Comment
Article Tags:
SQL
Databases

Explore