- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Advertisements