I want to copy table contained from one database and insert onto another database table

Question

I want to copy a table's schema as well as the data within that table to another database table in another database on a live server. How could I do this?

use two database connections and read table structure from one and run that query in to other — Dau, Jan 06 '12 at 07:21
Are these databases on separate mysql instances or the same one? Different instances could be setup with replication if this is a continuous process. — Nick, Jan 06 '12 at 07:23
@Nick my databases are on different mysql instances. Could you explain how to copy it? — johk95, Jun 10 '13 at 08:19

score 220 · Answer 1 · answered Jan 06 '12 at 07:28

220

If you want to copy a table from one Database to another database , You can simply do as below.

CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table SELECT * FROM db1.table;

answered Jan 06 '12 at 07:28

2

Great solution if the table has no foreign key constraints. I used this for making a copy of an entire database instead of just one table. For that I'd recommend using mysqldump command. – thorne51 Jun 18 '14 at 09:33
MySQL can now store table data in individual files (and the frm files are too per table). This approach would no doubt work, but with huge databases it is slow. Is there another way maybe? – Alex Kovshovik Jun 08 '15 at 16:58
1

This only works if the databases are on the same server. – zgr024 May 11 '17 at 19:08

score 10 · Answer 2 · answered Oct 02 '13 at 21:07

10

or just CREATE TABLE db2.table SELECT * FROM db1.table in MySQL 5

answered Oct 02 '13 at 21:07

HukeLau_DABA

2,384
6
33
51

4

This does not copy things such as index. It merely creates a table based on a set of tuples. You probably don't want to do this. – BenMQ Aug 26 '14 at 03:59
Yeah this query does not copies indexe – XxXk5XxX Oct 23 '14 at 01:55

score 4 · Answer 3 · answered Feb 03 '17 at 17:58

4

In BASH you can do:

mysqldump database_1 table | mysql database_2

answered Feb 03 '17 at 17:58

But those new buttons though..

21,377
10
81
108

score 2 · Answer 4 · answered Sep 22 '15 at 16:13

2

CREATE TABLE db2.table_new AS SELECT * FROM db1.table_old

answered Sep 22 '15 at 16:13

Rashi Goyal

933
9
15

score 2 · Answer 5 · answered Oct 28 '15 at 17:31

2

If you just want Structure to be copied simply use

CREATE TABLE Db_Name.table1 LIKE DbName.table2;

Ps > that will not copy schema and data

answered Oct 28 '15 at 17:31

Hemant Shori

2,463
1
22
20

Vishnu More · Answer 6 · 2015-08-13T09:27:52.030

1

simply use -

CREATE TABLE DB2.newtablename SELECT * FROM DB1.existingtablename;

edited Aug 13 '15 at 09:27

answered Aug 13 '15 at 06:48

Vishnu More

45
1
12

score 0 · Answer 7 · answered Jun 19 '20 at 09:17

In Commandline:

mysqldump -h localhost -u username -ppassword [SCHEMA] --tables [TABLE] | mysql -h otherhost -u username -ppassword [SCHEMA2]

This will copy table inside SCHEMA on localhost to SCHEMA2 on otherhost.

localhost and otherhost are just hostname and might be same or different.

I want to copy table contained from one database and insert onto another database table

7 Answers7

Linked

Related