MySQL – Duplicate a Table

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:

CREATE TABLE new_table AS SELECT * FROM old_table;

Example – Duplicate Table

Following is the students table’s structure.

And students table has the following data.

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

Let us see if both duplicate and original tables exist.

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.

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.

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

students table index

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.

CREATE TABLE pupils LIKE students; 
INSERT pupils SELECT * FROM students;

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

Conclusion

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