How To Clone a Table in MYSQL

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values, and so forth. Instead of spending time creating the exact same version of an existing table, you can create a clone of the existing table. Learn how to clone a table in MySQL effortlessly with our step-by-step guide. Duplicate your data securely and efficiently in just a few commands.

 

SQL Cloning Operation allows to creation of the exact copy of an existing table along with its definition. There are three types of cloning possible using SQL in various RDBMS; they are listed below −

• Simple Cloning
• Shallow Cloning
• Deep Cloning

 

1. Simple Cloning in MySQL

A simple cloning operation creates a new replica table from the existing table and copies all the records in the newly created table. To break this process down, a new table is created using the CREATE TABLE statement; and the data from the existing table, as a result of the SELECT statement, is copied into the new table. Explore the essential functions in MySQL with our comprehensive guide. Learn how to use built-in functions for data manipulation, queries, and more.

 

Here, the clone table inherits only the basic column definitions like the NULL settings and default values from the original table. It does not inherit the indices and AUTO_INCREMENT definitions.

 

Syntax : 

CREATE TABLE new_table SELECT * FROM original_table;

 

Example : 

CREATE TABLE emp_c1 SELECT * FROM employees;

 

2. Shallow Cloning in MySQL:

A shallow cloning operation creates a new replica table from the existing table but does not copy any data records into the newly created table, so only a new but empty table is created.

Here, the clone table contains only the structure of the original table along with the column attributes including indices and AUTO_INCREMENT definition.

Syntax : 

CREATE TABLE new_table LIKE original_table;

 

Example:

CREATE TABLE emp_c2 LIKE employees;

 

3. Deep Cloning in MySQL: 

Deep cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the contents from the existing table and all the attributes including indices and the AUTO_INCREMENT definitions. Master Regular Expressions in MySQL with our detailed guide. Learn how to efficiently search and manipulate text in your databases using powerful regex patterns.

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with a CREATE TABLE statement and one with an INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of the existing table, and INSERT INTO statement will insert the data from the existing table into the new table. 

 

Syntax : 

CREATE TABLE new_table LIKE original_table;

INSERT INTO new_table SELECT * FROM original_table;

 

Example:

CREATE TABLE emp_c3 LIKE employees;

INSERT INTO emp_c3  SELECT * FROM employees;

 

Do watch our Channel to learn more: Click Here

 

Author:

Mahesh Kankrale

Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd.