Cloning Table in SQL
Cloning a table in Database Management is a common requirement when you want to create an exact or partial copy of an existing table, including its structure, data or both as per your business requirement. This feature is particularly useful in scenarios like testing, backup or creating a template for similar data storage.This operation can save significant time and effort when replicating table structures, attributes and data.
In this blog, we will explore different methods for cloning tables based on different use cases. Depending on the use case, you may clone just the table structure, its data or both.
Demo Database
Let's create two tables Departments and Employees, and insert some records into them.
CREATE TABLE Departments (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100)
);
CREATE TABLE Employees (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Mobile VARCHAR(15) UNIQUE KEY,
DepartmentId INT,
Salary DECIMAL(10, 2),
CONSTRAINT FK_Department FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);INSERT INTO Departments (Name) VALUES ("Tech"),("Opr");
INSERT INTO Employees (Name, Mobile, DepartmentId, Salary)
VALUES
('Ram', '1234567890', 1, 50000.00),
('Rajesh', '9876543210', 2, 6000.00),
('Deepak', '5551234567', 1, 55000.00);Simple Cloning in SQL
Simple cloning creates a new table with the same structure as an existing table and copying all the data into it. This method includes basic column definitions (like NULL settings and default values) but does not copy indices, constraints or AUTO_INCREMENT properties.
CREATE TABLE NewTableName SELECT * FROM OriginalTableName;
OR
CREATE TABLE NewTableName AS SELECT * FROM OriginalTableName;
Explore with a Example
Let's clone Employees table into Staffs table
CREATE TABLE Staffs SELECT * FROM Employees;
Now , check data into Staffs table using SELECT query on Staffs table
SELECT * FROM Staffs;
Query Result
Id Name Mobile DepartmentId Salary 1 Ram 1234567890 1 50000.00 2 Rajesh 9876543210 2 6000.00 3 Deepak 5551234567 1 55000.00
Now, check Staffs table configuration using describe query
desc Staffs;
Describe Query Result
Field Type Null Key Default Extra Id int NO 0 Name varchar(100) NO Mobile varchar(15) YES DepartmentId int YES Salary decimal(10,2) YES
Note :- As we can see in Staffs table properties like NULL setting , Primary Key, Foreign Keys , Auto Increment etc are not copied from Original table. Only table structure with basic column details and data are copied form Original table.
Shallow Cloning in SQL
Shallow cloning creates a new table with the same structure as the original table but does not copy any data. This method also includes indices, constraints and AUTO_INCREMENT definitions.
CREATE TABLE NewTableName LIKE OriginalTableName;
Explore with a Example
Let's Shallow clone Employees table into Staffs table
CREATE TABLE Staffs Like Employees;
When we run SELECT query on Staffs there will be no records in Staffs table
Now, check Staffs table configuration using describe query
desc Staffs;
Result of Describe Query
Field Type Null Key Default Extra Id int NO PRI auto_increment Name varchar(100) NO Mobile varchar(15) YES UNI DepartmentId int YES MUL Salary decimal(10,2) YES
Note :- As we can see complete structure of Employees table has been copied into the Staffs table, except for data.
Deep Cloning in SQL
Deep cloning combines both simple and shallow cloning. It creates a new table with the same structure and copies all the data from the original table.
CREATE TABLE NewTableName LIKE OriginalTableName; INSERT INTO NewTableName SELECT * FROM OriginalTableName;
Explore with a Example
Deep clone of the Employees table into the Staffs table you query will be like
CREATE TABLE Staffs Like Employees; INSERT INTO Staffs SELECT * FROM Employees;
Now, run SELECT query to verify records in Staffs table.
SELECT * FROM Staffs;
Output of SELECT query
Id Name Mobile DepartmentId Salary 1 Ram 1234567890 1 50000.00 2 Rajesh 9876543210 2 6000.00 3 Deepak 5551234567 1 55000.00
Now check configuration/properties of Staffs table using describe query
DESC Staffs;
Result of Describe query
Field Type Null Key Default Extra Id int NO PRI auto_increment Name varchar(100) NO Mobile varchar(15) YES UNI DepartmentId int YES MUL Salary decimal(10,2) YES
Note :- As we can see complete structure of Employees table along with data has been copied into the Staffs table.
How to create a new table with basic structure ( not properties like null, Primary key etc) of a table with empty records
As we learned earlier, simple cloning creates a table with the basic structure along with all the data. so to achieve this we need to modify this process so that the newly created table is empty, we can achieve this in two ways
- Perform simple cloning and then truncate the table
- Perform simple cloning with a condition to exclude data.
1. Perform simple cloning and then Truncate the table
CREATE TABLE NewTableName SELECT * FROM OriginalTableName; TRUNCATE TABLE NewTableName ;
Let's understand this with an example
CREATE TABLE Staffs SELECT * FROM Employees; TRUNCATE TABLE Staffs;
Verification
When we run SELECT query on table there will be no data in table.
And When we run Describe Query on Staffs table
DESC Staffs;
Result will be as expected
Field Type Null Key Default Extra Id int NO 0 Name varchar(100) NO Mobile varchar(15) YES DepartmentId int YES Salary decimal(10,2) YES
2. Perform simple cloning with a condition to exclude data.
CREATE TABLE NewTableName SELECT * FROM OriginalTableName WHERE 1=0;
Let's understand this with an example
CREATE TABLE Staffs SELECT * FROM Employees WHERE 1=0;
Verification
When we run SELECT query on Staffs table the result set will be empty ( no records in table)
And When we run Describe Query on Staffs table
DESC Staffs;
Result will be as expected
Field Type Null Key Default Extra Id int NO 0 Name varchar(100) NO Mobile varchar(15) YES DepartmentId int YES Salary decimal(10,2) YES
Frequently Asked Questions (FAQs)
1. What does cloning a table mean in SQL?
Cloning a table in SQL means creating a new table based on an existing table’s structure, data or both. Depending on the method used, the cloned table may include only column definitions, only data or a full copy including indexes and constraints. Cloning is commonly used for testing, backups, reporting or creating similar tables quickly.
2. Why would I need to clone a table in real-world projects?
Cloning tables is useful in many real-world scenarios such as testing new features, creating backup copies, preparing reporting tables or experimenting with schema changes without touching production data. It helps developers work safely and efficiently while preserving the original table.
3. What is simple cloning in SQL?
Simple cloning creates a new table by copying column definitions and data from an existing table using CREATE TABLE AS SELECT. While it copies all rows, it does not copy indexes, primary keys, foreign keys, or auto-increment properties. This makes it suitable for quick data duplication but not for structural integrity.
4. What is shallow cloning and when should it be used?
Shallow cloning copies only the table structure, including columns, data types, indexes, constraints and auto-increment settings, but does not copy any data. This approach is ideal when you need an empty table with the same schema, such as for staging, logging or versioned tables.
5. What is deep cloning in SQL?
Deep cloning is a two-step process where you first copy the table structure and then insert all data from the original table. This results in a complete replica of the original table, including schema and records. It is commonly used when you need an exact working copy of a table.
6. Are indexes and constraints copied in all cloning methods?
No. Indexes, constraints and auto-increment properties are copied only in shallow cloning (CREATE TABLE LIKE). Simple cloning does not copy these properties. Deep cloning copies them because it starts with a shallow clone and then inserts data.
7. Does cloning a table copy foreign key relationships?
Foreign key definitions are copied only when using shallow or deep cloning methods that preserve table structure. However, the referenced parent tables must already exist. Simple cloning does not copy foreign key constraints at all.
8. Can I clone a table without copying any data?
Yes. You can clone only the structure without data using CREATE TABLE new_table LIKE original_table or by using CREATE TABLE AS SELECT … WHERE 1=0. Both approaches create an empty table, but the first preserves constraints while the second does not.
9. What is the safest way to clone a table in production?
In production environments, shallow cloning is often the safest approach because it avoids unnecessary data duplication and preserves structure. If data cloning is required, deep cloning should be done carefully with performance and storage considerations in mind, preferably during maintenance windows.
10. Does cloning a table affect the original table?
No. Cloning operations do not modify the original table in any way. They only create a new table based on the original. However, cloned tables increase storage usage and may require maintenance such as indexing or permissions setup.
11. How does auto-increment behave in cloned tables?
In shallow and deep cloning, auto-increment properties are preserved, and the counter continues independently in the new table. In simple cloning, auto-increment is not copied and the column becomes a normal integer field without automatic increment behavior.
12. Can I clone only selected columns from a table?
Yes. You can clone a subset of columns using CREATE TABLE AS SELECT column1, column2 FROM original_table. This creates a new table with only the selected columns and their data. Constraints and indexes are not copied in this approach.
13. Is table cloning supported in all SQL databases?
Most relational databases support table cloning, but syntax and behavior vary. MySQL supports CREATE TABLE AS and LIKE. PostgreSQL and Oracle have similar capabilities. SQL Server uses SELECT INTO for data cloning. Always check database-specific behavior before implementation.
14. Is cloning tables a common SQL interview topic?
Yes. Cloning tables is frequently discussed in SQL interviews to test understanding of schema design, constraints and data duplication. Interviewers often ask about differences between copying structure only, data only or both and the implications of each method.
15. What are common mistakes when cloning tables?
Common mistakes include assuming constraints are copied when they are not, cloning large tables without considering performance, forgetting to recreate indexes and using cloning directly in production without testing. Understanding the differences between cloning methods helps avoid these issues.