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.
- Duplicate only structure and data of the table
- 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.
students table has the following data.
Now we shall copy the structure and data of
students into a new table
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
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
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
In this MySQL Tutorial, we learned to duplicate a table (structure and data, optionally index and triggers).