SQL - Clone Tables

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 on creating the exact same version of an existing table using the CREATE TABLE statement, you can clone the table without disturbing the original table.

Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table. This operation comes in handy during testing processes, where there is a need to perform sample testing using the existing database tables.

There are three types of cloning possible using SQL in various RDBMS; they are psted below −

Simple Cloning − Creates a new table without copying any constraints or indexes etc.

Shallow Cloning − Creates a new empty table with the same table structure of an existing table.

Deep Cloning − Creates a new table and copies the table structure and data of an existing table to the new table.

However, there are still no direct ways to fully clone a table in an SQL server. You have to perform a sequence of steps to clone a table including its definition and data.

Steps to Clone a Table

Following are the two general ways to copy a table in an SQL server. To fully clone a table, perform the two methods together.

Generate the CREATE TABLE script to copy the structure of a table.

Using the SELECT... INTO statement to copy the data of a table.

SQL actually provides various methods to clone a table, but not all methods are supported by all RDBMS . We will discuss how to clone a table in MySQL database later in this chapter.

Generating a CREATE TABLE Script in SQL Server

To clone the structure of a table void of any data in it, you can generate a CREATE TABLE script in an SQL Server. In this generated script, you can change the name of the table or any references that you wish to be changed. A new table with exact same definition of the original table will be created. However, the records must be inserted into this table later using an INSERT statement.

Generating a script becomes easier if done with the help of GUI interfaces pke SQL Server Management System etc. Follow the given steps below to clone the structure of an existing table into a new table, using the SSMS interface.

Right cpck on the database table (Databases > Your database > Tables > Your table) you wish to clone in the SSMS Object Explorer.

Select the "Script Table As" option followed by the "CREATE TO" option.

You can now use the generated script to create the table either in a New Query Window, new file or copy the script to the Cppboard.

Example

In this example, let us try to clone the structure of existing CUSTOMERS table in the database using the script generated by following the steps mentioned above.

The script would be generated as −

USE [sampleDB]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 23-02-2023 11:29:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CUSTOMERS](
	[ID] [int] NOT NULL,
	[NAME] [varchar](20) NOT NULL,
	[AGE] [int] NOT NULL,
	[ADDRESS] [char](25) NULL,
	[SALARY] [decimal](18, 2) NULL,
   PRIMARY KEY CLUSTERED 
   (
      [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
   OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

In the CREATE TABLE statement of the script above, we are changing the name "CUSTOMERS" to "testCUSTOMERS". The script will look as −

USE [sampleDB]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 23-02-2023 11:29:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[testCUSTOMERS](
	[ID] [int] NOT NULL,
	[NAME] [varchar](20) NOT NULL,
	[AGE] [int] NOT NULL,
	[ADDRESS] [char](25) NULL,
	[SALARY] [decimal](18, 2) NULL,
   PRIMARY KEY CLUSTERED 
   (
      [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Output

On executing the script above, output will be displayed as given below −

Commands completed successfully.

Verification

To verify whether the table is created successfully or not, use the following query −

SELECT * FROM testCUSTOMERS;

The table will be displayed as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

With SELECT...INTO STATEMENT

The SELECT...INTO statement creates a new table and copies the data from an existing table into it. However, this command only copies the data and not the definition of it, thus, omitting constraints, indexes etc., if any. They need to be added separately if one wishes to have the exact same structure of the original table in their new table.

Note − The SELECT...INTO command can copy a table within the same database and across databases as well.

Syntax

Following is the basic syntax of the SELECT... INTO statement −

SELECT * INTO new_table FROM original_table;

Example

The CUSTOMERS table in the database that contains personal details of customers of an organization. Following is the CUSTOMERS table −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, we want to clone the data of this table into a new table testCUSTOMERS. To do that, we are using the query as shown below −

SELECT * INTO testCUSTOMERS FROM CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To verify whether all the data is copied into the new table testCUSTOMERS, we shall use the SELECT statement as follows −

SELECT * FROM testCUSTOMERS;

The table will be displayed as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Cloning a Table in MySQL RDBMS

If you are using MySQL RDBMS, a basic way to handle this situation is by adhering to the steps given below −

Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table s structure, indexes and all.

Modify the statement to change the table name to that of the clone table and execute the statement. This way you will have an exact clone table.

Optionally, if you need the table contents copied as well, issue an INSERT INTO or a SELECT statement too.

Example

Try out the following example to create a clone table for TUTORIALS_TBL whose structure is as follows −

Step 1 − Get the complete structure about the table.

SQL> SHOW CREATE TABLE TUTORIALS_TBL G; 
*************************** 1. row *************************** 
      Table: TUTORIALS_TBL 
Create Table: CREATE TABLE  TUTORIALS_TBL  ( 
   tutorial_id  int(11) NOT NULL auto_increment, 
   tutorial_title  varchar(100) NOT NULL default   , 
   tutorial_author  varchar(40) NOT NULL default   , 
   submission_date  date default NULL, 
  PRIMARY KEY  ( tutorial_id ), 
  UNIQUE KEY  AUTHOR_INDEX  ( tutorial_author ) 
) TYPE = MyISAM 
1 row in set (0.00 sec)

Step 2 − Rename this table and create another table.

SQL> CREATE TABLE `CLONE_TBL` ( 
  ->  tutorial_id  int(11) NOT NULL auto_increment, 
  ->  tutorial_title  varchar(100) NOT NULL default   , 
  ->  tutorial_author  varchar(40) NOT NULL default   , 
  ->  submission_date  date default NULL, 
  -> PRIMARY KEY  (`tutorial_id ), 
  -> UNIQUE KEY  AUTHOR_INDEX  ( tutorial_author ) 
-> ) TYPE = MyISAM; 
Query OK, 0 rows affected (1.80 sec) 

Step 3 − After executing step 2, you will clone a table in your database. If you want to copy data from an old table, then you can do it by using the INSERT INTO... SELECT statement.

SQL> INSERT INTO CLONE_TBL (tutorial_id, 
   ->                        tutorial_title, 
   ->                        tutorial_author, 
   ->                        submission_date) 
   -> SELECT tutorial_id,tutorial_title, 
   ->        tutorial_author,submission_date, 
   -> FROM TUTORIALS_TBL; 
Query OK, 3 rows affected (0.07 sec) 
Records: 3  Duppcates: 0  Warnings: 0 

Finally, you will have an exact clone table as you wanted to have. But to make this process simpler, we can try to perform Simple Cloning, Shallow Cloning or Deep Cloning using CREATE TABLE and INSERT INTO statements.

Simple Cloning in MySQL

Simple Cloning operation only copies the data from the existing table and copies them into the new table created. To break this down, a new table is created using the CREATE TABLE statement; and the data from the selected columns of an existing table, as a result of SELECT statement, is copied into the new table.

Syntax

Following is the basic syntax to perform simple cloning in MySQL RDBMS −

CREATE TABLE new_table SELECT * FROM original_table;

Example

In the following example, we are trying to perform simple cloning operation on the given CUSTOMERS table.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

With the following query, we are trying to create a new table "testCUSTOMERS" by cloning the "CUSTOMERS" table.

CREATE TABLE testCUSTOMERS SELECT * FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.06 sec)
Records: 7  Duppcates: 0  Warnings: 0

Verification

To verify whether the new table contains all the information from the existing table CUSTOMERS, we can use the following SELECT query −

SELECT * FROM testCUSTOMERS;

The testCUSTOMERS table will be retrieved as shown −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Shallow Cloning in MySQL

Shallow Cloning operation only copies the structure of the existing table into the new table created. This operation excludes the data in the existing table so only an empty new table can be created.

Syntax

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

CREATE TABLE new_table LIKE original_table;

Example

In the following example, we are trying to perform shallow cloning operation on the given CUSTOMERS table.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

With the following query, we are trying to create a new table "testCUSTOMERS1" by cloning the "CUSTOMERS" table.

CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;

Output

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Verification

To verify whether the new table is created or not, we can use the following SELECT query −

SELECT * FROM testCUSTOMERS1;

The testCUSTOMERS table will be retrieved as shown −

Empty set (0.00 sec)

A new table is thus cloned from the existing table with no data copied into it.

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 attributes of the existing table and also its contents.

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

Example

In the following example, we are trying to perform deep cloning operation on the given CUSTOMERS table.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

With the following query, we are trying to create a new table "testCUSTOMERS2" by cloning the "CUSTOMERS" table, i.e. perform shallow cloning first.

CREATE TABLE testCUSTOMERS2 LIKE CUSTOMERS;

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Now using the following query, we are trying to insert data from "CUSTOMERS" table into new table "testCUSTOMERS2", i.e. performing simple cloning.

INSERT INTO testCUSTOMERS2 SELECT * FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duppcates: 0  Warnings: 0

Verification

To verify whether the new table is created or not with all the data present in it, we can use the following SELECT query −

SELECT * FROM testCUSTOMERS2;

The testCUSTOMERS table will be retrieved as shown −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+