- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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
Let us create the first table in the “test” database
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
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)
- Related Articles
- Display two different columns from two different tables with ORDER BY?
- Concatenate columns from different tables in MySQL
- Swap data between two columns in MySQL?
- How to get row count of two tables in different databases in a single query?
- How to find all tables that contains two specific columns in MySQL?
- How can we compare data in two MySQL tables?
- How to GRANT SELECT ON all tables in all databases on a server with MySQL?
- How to find missing value between two MySQL Tables?
- MySQL query to sum the values of similar columns from two different tables for a particular ID
- Getting Information About MySQL Databases and Tables
- How to copy tables or databases from one MySQL server to another MySQL server?
- How to calculate the difference between time in different MySQL columns?
- Get the time difference between values in different columns with MySQL
- Custom sorting using two different columns in MySQL?
- Merge two tables with union in MySQL?