How can I clone/duplicate the table along with its data, trigger and indexes?

For creating a new table just like old one along with its data, trigger, and indexes, we need to run following two queries

CREATE TABLE new_table LIKE old_table;
INSERT new_table SELECT * from old_table;

Example

mysql> Create table employee(ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(20));
Query OK, 0 rows affected (0.21 sec)

mysql> Describe employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)

mysql> Insert into employee(name) values('Gaurav'),('Raman');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Select * from employee;
+----+--------+
| ID | NAME   |
+----+--------+
| 1  | Gaurav |
| 2  | Raman  |
+----+--------+
2 rows in set (0.00 sec)

The query below will create the table employee1 having a similar structure as table employee. It can be checked by running the DESCRIBE query.

mysql> create table employee1 like employee;
Query OK, 0 rows affected (0.19 sec)

mysql> describe employee1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| NAME  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.14 sec)

Now the query below will insert same values into employee1 as in employee which can be checked as below

mysql> INSERT INTO employee1 select * from employee;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from employee1;
+----+--------+
| ID | NAME   |
+----+--------+
| 1  | Gaurav |
| 2  | Raman  |
+----+--------+
2 rows in set (0.00 sec)

In this way, we can make the clone of the table along with its data, triggers, and indexes too.

Sharon Christine

Updated on 20-Jun-2020 06:06:40

• Related Questions & Answers
• How to clone a MySQL table, indexes, and data?
• How can I use SPACE() function along with MySQL’s column data?
• How can we make a MySQL clone table?
• How do I clone the structure of a table in MySQL?
• How can I view the indexes I have set up in MySQL?
• How can I trigger a JavaScript click event?
• If I truncate a table, should I also add indexes?
• How can we export data to a CSV file along with columns heading as its first line?
• What happens with the trigger when we will drop the table having that trigger?
• How to create a MySQL table with indexes?
• How can I trigger an onchange event manually in javascript?
• How can I transfer data from SAP HANA to virtual table?
• How can MySQL find and replace the data with REPLACE() function to UPDATE the table?
• MongoDB transaction & indexes for duplicate values
• How can I check the list of MySQL tables, in the current database we are using, along with table type in the result set?