Duplicate Table in MySQL

You can copy or duplicate MySQL table to another table. Duplicating MySQL table is useful when you would like to run some experiments on the table, but want to have a backup of the table. Or in some scenarios, you would like to create a table with exact columns and indexes of an already existing table.

There are two levels to duplicate a MySQL table based on the totality of duplicating table.

  1. Duplicate only structure and data of the table
  2. Duplicate structure and data of the table along with indexes and triggers.

Duplicate structure and data of MySQL Table

Columns and their properties such as datatypes, default values, charactersets, etc., is called structure of the tables. And rows in that table is data.

To duplicate structure and data of MySQL Table, CREATE a new TABLE with the data selected from the previous table. Following is the syntax:

</>
Copy
CREATE TABLE new_table AS SELECT * FROM old_table;

Example – Duplicate Table

Following is the students table’s structure.

Describe MySQL Table structure

And students table has the following data.

show mysql table data

Now we shall copy the structure and data of students into a new table pupils.

MySQL Duplicate Table with Structure and Data

Let us see if both duplicate and original tables exist.

MySQL SHOW TABLES

Yeah. There they are. And it is time to check if the structure and data of pupils table is exactly same as that of students.

MySQL Duplicated Table Structure
MySQL Duplicated Table Data

Thats it for the first level of duplication! We have successfully duplicated a table’s structure and rows in it.

Duplicate structure and data of the table along with indexes and triggers

To duplicate MySQL Table’s indexes and triggers also along with structure and data, you have to run two SQL Queries in mysql command line interface.

</>
Copy
CREATE TABLE new_table LIKE old_table; 
INSERT new_table SELECT * FROM old_table;
  • The first query creates a table with the same structure, index and triggers as of the old table.
  • The second query copies the data from old table to new table.

To check the effect on index, we shall add a new column to mysql table students and try duplicating the table.

students table structure

MySQL after adding a new column to the table

students table index

MySQL show index of table

Now we shall try to duplicate all of this table (data, structure, index and triggers) into a new table called pupils.

Run the following two queries.

</>
Copy
CREATE TABLE pupils LIKE students; 
INSERT pupils SELECT * FROM students;
MySQL Copy table with index

Let us see what happened to the index of newly created table pupils.

MySQL Index of Duplicated Table

Conclusion

In this MySQL Tutorial, we learned to duplicate a table (structure and data, optionally index and triggers).