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;
Let us create the table using deep clone method
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.
INSERT INTO STUDENT_DEEP_CLONE (name,roll_no)
VALUES ('mohini roy', 'S400');
INSERT INTO STUDENT_DEEP_CLONE (name,roll_no)
VALUES ('surbhi roy', 'S500');
Output:
Explanation: In the above output, we performed the INSERT operation for two entries to the “STUDENT_DEEP_CLONE” table to validate the functionality or properties of the indices and the AUTO_INCREMENT function. Finally, we clone the sql table.
SQL Cloning or Copying a Table
Cloning or copying a table in SQL is a common task encountered in database management. Whether you’re creating backups, performing testing, or need to duplicate a table structure for various purposes, knowing how to effectively clone or copy a table is essential. In this article, we’ll explore different methods and good practices for achieving this in SQL.
Cloning tables is an operation in SQL that allows us to make a copy of an existing table. The clone table can be just the structure of the original table without any data or an exact copy of the original table.
Note: This Article will be following the MySQL Syntax but cloning operations can be done in other Relational Database Management systems (RDBMS) such as Postgre SQL, and Microsoft SQL Server, and syntax may follow as per their document.