Simple Cloning
In this method, the clone table only inherits the basic structure, default values, and NULL settings but it does not inherit the indices and AUTO_INCREMENT.
Syntax:
CREATE TABLE clone_table SELECT * FROM original_table;
Let us see the example to understand how simple cloning syntax works
CREATE TABLE STUDENT_COPY SELECT * FROM STUDENT;
Let’s see whether the cloning of the STUDENT_COPY is successfully executed or not;
SELECT * FROM STUDENT_COPY;
Output:
Cloning of the table
Let’s see the property of both the tables STUDENT and STUDENT_COPY respectively
Property inherits while cloning the sql tables
As we can see that in original table “STUDENT”, we have primary Key and auto_increment command for student_id and unique key for roll_no but in “STUDENT_COPY” clone table we do not have the primary key, auto_increment, and unique key respectively.
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.
Output:
Drawback of simple cloning
Explanation: In the above output, you can see that in the original table “STUDENT” we had set the student_no as a primary key but now in the simple clone table “STUDENT_COPY” values, there are duplicate value for the last two entries and Auto_increment command also becomes invalid here. To avoid this, we will be using Shallow cloning technique.
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.