
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- 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 trigger a JavaScript click event?
- How can I view the indexes I have set up in MySQL?
- How to create a MySQL table with indexes?
- How can we export data to a CSV file along with columns heading as its first line?
- If I truncate a table, should I also add indexes?
- What happens with the trigger when we will drop the table having that trigger?
- MongoDB transaction & indexes for duplicate values
- 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?
- How can I check the list of MySQL tables, in the current database we are using, along with table type in the result set?

Advertisements