SQL COPY TABLE

Let’s discuss why we use SQL COPY TABLE, 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.

What is a Cloning Table in SQL and When do We Use it?

Cloning is an operation in SQL that allows us to make a copy of an existing table, thereby creating a new table that can be exactly a duplicate copy along with the data from the original table or might just have the structure of the original table without any data. The new table thus created is called a clone table in SQL.

Let us assume a scenario where you are developing an app that involves some databases, and there is a situation where you need a table that is similar to a pre-existing table. This might be due to the fact that some new features are being implemented or some tests are to be done on the existing table. So, by any means, you would not like the original table to be tampered with. In such a situation we make a clone table in SQL, thereby saving our time and effort of creating an entirely new table and entering all the same data once again.

How to Clone Tables in SQL?

There are mainly three methods to create a clone table in SQL, namely:

• Simple cloning
• Shallow cloning
• Deep cloning

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.

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

Following is the basic syntax to perform simple cloning in MySQL

CREATE TABLE new_table SELECT * FROM original_table;

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. This results in the creation of a new, empty table.

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

Syntax

Following is the basic syntax to perform shallow cloning in MySQL RDBMS

CREATE TABLE new_table LIKE original_table;

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.

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 the INSERT INTO statement will insert the data from the existing table into the new table.

Syntax

Following is the basic syntax to perform deep cloning in MySQL RDBMS

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Conclusion

In conclusion, while SQL does not have a specific COPY TABLE statement, you can achieve the same result by using the INSERT INTO ... SELECT statement. This allows you to copy data from one table to another within the same database or even across databases, provided that the destination table has a compatible column structure with the source table. By specifying the columns you want to copy and any optional conditions, you can effectively transfer data between tables in SQL. This flexibility makes it easy to manipulate and manage data within a database environment.

Frequently asked questions