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