MySQL - 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 MySQL RDBMS; they are listed below −

• Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.

• Shallow Cloning: Creates a new empty table with the same table definition 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.

Cloning Tables in MySQL

You can handle this situation by following the steps given below −

• Use SHOW CREATE TABLE 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 the exact clone table.

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

Example

Try out the following example to create a clone table for CUSTOMERS.

Step 1 − First of all, create the CUSTOMERS using the CREATE TABLE statement.

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Insert some records into it using the following INSERT INTO statement.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Now, execute the following SHOW CREATE TABLE command to display the source table's (CUSTOMERS) structure.

SHOW CREATE TABLE CUSTOMERS;

Following is the CUSTOMERS table structure.

Table: CUSTOMERS
Create Table: CREATE TABLE `customers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Step 2 − Now, copy the above source table structure and just change the name to copyCUSTOMERS.

CREATE TABLE `copyCUSTOMERS` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Step 3 − After executing step 2, if we want to copy data from the source table then we can do it by using INSERT INTO... SELECT statement.

INSERT INTO copyCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
   
SELECT ID,NAME,AGE,ADDRESS,SALARY FROM CUSTOMERS;

Let us verify whether the clone table copyCUSTOMERS has the same records as CUSTOMERS table or not using the following query −

SELECT * FROM copyCUSTOMERS;

As we observe the output, we have an exact clone table as CUSTOMERS.

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. Let us discuss them one by one in detail with suitable examples further in this tutorial.

Simple Cloning in MySQL

Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.

Syntax

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

CREATE TABLE new_table SELECT * FROM original_table;

Example

To perform the simple cloning operation, let us first create a table named CUSTOMERS table using the below query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To display the records of the above created table, execute the following query −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, with the following query, we are creating a new table CUSTOMERScopy by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 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 CUSTOMERScopy;

Following is the CUSTOMERScopy table −

Shallow Cloning in MySQL

The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn't copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.

Syntax

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

CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;

Example

In the following query, we are performing shallow cloning by creating a new table named CUSTOMERScopy1 by cloning the CUSTOMERS table.

CREATE TABLE CUSTOMERScopy1 LIKE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)

Verification

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

SELECT * FROM CUSTOMERScopy1;

As we can see in the output, the new table CUSTOMERScopy1 has been created with no data copied into it.

Empty set (0.00 sec)

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 query, we are creating a new table CUSTOMERScopy2 by cloning the CUSTOMERS table, i.e. perform shallow cloning first.

CREATE TABLE CUSTOMERScopy2 LIKE CUSTOMERS;

Now using the following query, we are inserting data from CUSTOMERS table into new table CUSTOMERScopy2, i.e. performing simple cloning.

INSERT INTO CUSTOMERScopy2 SELECT * FROM CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 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 CUSTOMERScopy2;

As we observe the ouptut, the CUSTOMERScopy2 table has the same structure and data as CUSTOMERS table.

Cloning a table Using a Client Program

Besides using MySQL queries to clone an existing table, you can also perform the cloning operation on a table using a client program.

Syntax

Following are the syntaxes to clone a table various in programming languages −

$sql = "CREATE TABLE clone_table LIKE tut_tbl";
$mysqli->query($sql);

sql = "CREATE TABLE new_table SELECT * FROM original_table";
con.query(sql);

String sql = "CREATE TABLE clone_table LIKE tut_tbl";
statement.executeUpdate(sql);

sql = "CREATE TABLE new_table_name AS
   SELECT * FROM source_table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
printf('Connected successfully.
');

// clone table
// To inherit all table definitions, use the create table...like syntax
$sql = "CREATE TABLE clone_table LIKE tut_tbl";
if ($mysqli->query($sql)) {
    printf("Table cloned successfully.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned: %s
", $mysqli->error);
}

// To copy the data, you'll need INSER...SELECT
$cpy_data = "INSERT INTO clone_table SELECT * FROM tut_tbl";
if ($mysqli->query($cpy_data)) {
    printf("fully cloned including data.
");
}
if ($mysqli->errno) {
    printf("table could not be cloned fully: %s
", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Table cloned successfully.
fully cloned including data.

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "CREATE DATABASE TUTORIALS;"
  con.query(sql);

  sql = "USE TUTORIALS;"
  con.query(sql);

  sql = "CREATE TABLE CUSTOMERS(ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) );"
  con.query(sql);

  sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh',32, 'Ahmedabad', 2000.00 ),(2, 'Khilan',25, 'Delhi', 1500.00 ),(3, 'kaushik',23, 'Kota', 2000.00),(4,'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik',27, 'Bhopal', 8500.00 ),(6, 'Komal',22, 'MP', 4500.00 ),(7, 'Muffy',24, 'Indore', 10000.00 );"
  con.query(sql);

  sql = "CREATE TABLE testCUSTOMERS SELECT * FROM CUSTOMERS;"
  con.query(sql);

  sql = "SELECT * FROM testCUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**Records in Clone table**")
    console.log(result);
  });
});

Connected!
--------------------------
**Records in Clone table**
[
  {
    ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: 'Ahmedabad',
    SALARY: '2000.00'
  },
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Delhi',
    SALARY: '1500.00'
  },
  {
    ID: 3,
    NAME: 'kaushik',
    AGE: 23,
    ADDRESS: 'Kota',
    SALARY: '2000.00'
  },
  {
    ID: 4,
    NAME: 'Chaitali',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  },
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Bhopal',
    SALARY: '8500.00'
  },
  { ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' },
  {
    ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: 'Indore',
    SALARY: '10000.00'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CloneTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Creates clone tables...!
            String sql = "CREATE TABLE clone_tbl LIKE customers";
            statement.executeUpdate(sql);
            System.out.println("Clone table of customers has been created successfully...!");
            
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Clone table of customers has been created successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
source_table_name = 'tutorials_tbl'
new_table_name = 'tutorials_tbl_cloned'
#Creating a cursor object 
cursorObj = connection.cursor()
cursorObj.execute(f"CREATE TABLE {new_table_name} AS SELECT * FROM {source_table_name}")
print(f"Table '{source_table_name}' is cloned to '{new_table_name}' successfully.")
cursorObj.close()
connection.close()

Table 'tutorials_tbl' is cloned to 'tutorials_tbl_cloned' successfully.