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 to move data between two tables with columns in different MySQL databases?
For this, you need to use an INSERT SELECT statement. The syntax is as follows
INSERT INTO yourDatabaseName1.yourTableName1(yourColumnName1,yourColumnName2,....N) SELECT yourColumnName1,yourColumnName2,....N FROM yourdatabaseName2.yourTableName2;
Here, I am using the following two databases
- sample
- test
Let us create the first table in the “test” database
<send>
mysql> use test; Database changed mysql> create table send -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.19 sec)
Insert some records in the first table using insert command. The query is as follows
mysql> insert into send(Name) values('John');
Query OK, 1 row affected (0.20 sec)
mysql> insert into send(Name) values('Carol');
Query OK, 1 row affected (0.40 sec)Display all records from the table using a select statement
mysql> select *from send;
The following is the output
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | +----+-------+ 2 rows in set (0.00 sec)
Now, create the second table in the “sample” database
<receive>
mysql> use sample; Database changed mysql> create table receive -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.59 sec)
As you can see above, we do not have a record in the second table “receive”.
Let us now move data between 2 tables with different columns in different databases. The query is as follows
mysql> insert into sample.receive(UserId,UserName) -> select Id,Name from test.send; Query OK, 2 rows affected (0.21 sec) Records: 2 Duplicates: 0 Warnings: 0
Display all records from the table sample.receive. The query is as follows.
mysql> select *from receive;
The following is the output displaying we have successfully moved the date from one table to another in different databases
+--------+----------+ | UserId | UserName | +--------+----------+ | 1 | John | | 2 | Carol | +--------+----------+ 2 rows in set (0.00 sec)
453 Views
