Move Data Between Two Tables with Different Columns in 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)