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
MySQL command to copy table?
You can achieve this with the help of INSERT INTO SELECT statement. The syntax is as follows −
INSERT INTO yourDatabaseName.yourTableName(SELECT *FROM yourDatabaseName.yourTableName);
To understand the above syntax, let us create a table in a database and second table in another database
The database name is “bothinnodbandmyisam”. Let us create a table in the same database. The query is as follows −
mysql> create table Student_Information -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(10), -> Age int -> ); Query OK, 0 rows affected (0.67 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into Student_Information(Name,Age) values('Larry',30);
Query OK, 1 row affected (0.20 sec)
mysql> insert into Student_Information(Name,Age) values('Mike',26);
Query OK, 1 row affected (0.19 sec)
mysql> insert into Student_Information(Name,Age) values('Bob',26);
Query OK, 1 row affected (0.12 sec)
mysql> insert into Student_Information(Name,Age) values('Carol',24);
Query OK, 1 row affected (0.15 sec)Now you can display all records from the table using select statement. The query is as follows −
mysql> select *from Student_Information;
The following is the output −
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 1 | Larry | 30 | | 2 | Mike | 26 | | 3 | Bob | 26 | | 4 | Carol | 24 | +----+-------+------+ 4 rows in set (0.00 sec)
Here is the second database −
mysql> use sample; Database changed
Now create only a single table in this database. The query is as follows −
mysql> create table Student_Table_sample -> ( -> StudentId int NOT NULL AUTO_INCREMENT, -> StudentName varchar(20), -> StudentAge int , -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (0.57 sec)
Here is the command to copy a table. The query is as follows −
mysql> insert into sample.Student_Table_sample(select *from bothinnodbandmyisam.Student_Information); Query OK, 4 rows affected (0.23 sec) Records: 4 Duplicates: 0 Warnings: 0
Four records got affected that means the table is copied successfully. The query is as follows to display all records from the second table ‘Student_Table_sample’.
The query is as follows −
mysql> select *from Student_Table_sample;
The following is the output displaying records from a table in another database −
+-----------+-------------+------------+ | StudentId | StudentName | StudentAge | +-----------+-------------+------------+ | 1 | Larry | 30 | | 2 | Mike | 26 | | 3 | Bob | 26 | | 4 | Carol | 24 | +-----------+-------------+------------+ 4 rows in set (0.00 sec)
280 Views
