- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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 to clone a MySQL table, indexes, and data?
To clone a MySQL table, indexes and data, we can use “LIKE”. I have a database ‘business’ and in that many tables have been created. Here we can check all the tables.
mysql> USE business; Database changed
Let us display all the tables −
mysql> SHOW tables; +------------------------+ | Tables_in_business | +------------------------+ | addcolumntable | | autoincrement | | autoincrementtable | | bookindexes | | chardemo | | columnvaluenulldemo | | dateadddemo | | deletedemo | | deleterecord | | demo | | demo1 | | demoascii | | demoauto | | demobcrypt | | demoemptyandnull | | demoint | | demoonreplace | | demoschema | | distinctdemo | | duplicatebookindexes | | duplicatefound | | employeetable | | existsrowdemo | | findandreplacedemo | | foreigntable | | foreigntabledemo | | groupdemo | | groupdemo1 | | incasesensdemo | | int1demo | | intdemo | | latandlangdemo | | limitoffsetdemo | | milliseconddemo | | modifycolumnnamedemo | | modifydatatype | | moneydemo | | moviecollection | | mytable | | nonasciidemo | | nthrecorddemo | | nulldemo | | nullwithselect | | pasthistory | | presenthistory | | primarytable | | primarytable1 | | primarytabledemo | | sequencedemo | | smallintdemo | | spacecolumn | | student | | tblstudent | | tbluni | | textdemo | | texturl | | trailingandleadingdemo | | unsigneddemo | | varchardemo | | varchardemo1 | | varchardemo2 | | varcharurl | | whereconditon | +------------------------+ 63 rows in set (0.25 sec)
Now, I am using the table STUDENT from the above database. The query is as follows −
mysql> DESC student;
The following is the output
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.20 sec)
Let us now check the records of the student table:
SELECT * from student
Here is the output:
+------+------+ | id | Name | +------+------+ | 1 | John | | 2 | Bob | +------+------+ 2 rows in set (0.00 sec)
Here is the query; we can make clone of indexes as well as data using LIKE. The query is as follows −
mysql> CREATE table cloneStudent like student; Query OK, 0 rows affected (1.57 sec) mysql> insert cloneStudent SELECT *from student; Query OK, 2 rows affected (0.60 sec) Records: 2 Duplicates: 0 Warnings: 0
Therefore, I have successfully created clone as well as data. Now, we can prove that we have created clone of indexes as well as data with the help of DESC command, which is as follows −
mysql> DESC cloneStudent;
The following is the output
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Let us check whether the table is cloned or not.
mysql> SELECT * from cloneStudent;
The following is the output:
+------+------+ | id | Name | +------+------+ | 1 | John | | 2 | Bob | +------+------+ 2 rows in set (0.00 sec)
- Related Articles
- How can I clone/duplicate the table along with its data, trigger and indexes?
- How to create a MySQL table with indexes?
- How can we make a MySQL clone table?
- Add new MySQL table columns and create indexes?
- How do I clone the structure of a table in MySQL?
- How to get a list of MySQL indexes?
- How can we check the indexes created by a UNIQUE constraint on a MySQL table?
- How to create a clone of a data frame in R without data values?
- How to write MySQL procedure to insert data into a table?
- How to find out all the indexes for a DB2 table TAB1?
- How can we insert data into a MySQL table?
- MySQL statement to copy data from one table and insert into another table
- How to select data from a table where the table name has blank spaces in MYSQL?
- How to clone a GitHub repository?
- Show/view indexes in a MySQL Database
