MySQL Copy Table

Last Updated: 28 May, 2023

In MySQL, we can create a duplicate table of an existing table with the same table structure, indexes, constraints, default values, etc. The duplicate table name and the existing table name will be different.

We use the CREATE TABLE and SELECT statements to create a duplicate table. We can also use the WHERE clause if we need to create a duplicate table for some specific types of records.

The duplicate table and the original table are independent of each other, and if we perform any operation on either table, that will not affect the other table.

Let's see the syntax and example of creating a copy of an existing table in MySQL.

Syntax:

  CREATE TABLE new_table_name  
  SELECT column1, column2, columnN
  FROM existing_table_name;

Example:

  CREATE TABLE student_bk
  SELECT student_id, student_name, roll_number
  FROM student;

After executing the query, MySQL first creates a student_bk table. The structure of the student_bk table is defined by the result set of the SELECT statement. Then, MySQL stores records that come from the SELECT statement in the student table.

Before creating a duplicate table, first we need to make sure that the duplicate table name must be unique in the database. In order to avoid error, we can use the IF NOT EXISTS clause with the CREATE TABLE statement.

Let's see the syntax and example of creating a copy of an existing table with IF NOT EXISTS caluse in MySQL.

Syntax:

  CREATE TABLE IF NOT EXISTS new_table_name  
  SELECT column1, column2, columnN
  FROM existing_table_name;

Example:

  CREATE TABLE IF NOT EXISTS student_bk
  SELECT student_id, student_name, roll_number
  FROM student;

In a situation where we need to contain only some specific types of records in the duplicate table then we add WHERE clause with CREATE TABLE statement.

Let's see the syntax of create a duplicate table with WHERE clause.

Syntax:

  CREATE TABLE new_table_name  
  SELECT column1, column2, columnN
  FROM existing_table_name
  WHERE condition;

Example:

  CREATE TABLE IF NOT EXISTS student_bk
  SELECT student_id, student_name, roll_number
  FROM student
  WHERE roll_number > 50 AND roll_number   100;

Why do we need to create a duplicate table?

There are a couple of reasons why we need to create a duplicate table in the database. Let's have a look at the reasons given below:

1. Back of data in table failure.
2. Perform some operation on the data without affecting the original table data, for example, replicating the production data for testing.