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)