CODEWITHSUNDEEP
sqlmysql
user1031092
user1031092

Reputation: 989

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

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?

Upvotes: 98

Views: 87252

Answers (7)

DrGeneral
DrGeneral

Reputation: 2142

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.

Upvotes: 0

You Old Fool
You Old Fool

Reputation: 22959

In BASH you can do:

mysqldump database_1 table | mysql database_2

Upvotes: 4

Hemant Shori
Hemant Shori

Reputation: 2493

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

Upvotes: 2

Rashi Goyal
Rashi Goyal

Reputation: 941

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

Upvotes: 2

Vishnu More
Vishnu More

Reputation: 45

simply use -

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

Upvotes: 1

HukeLau_DABA
HukeLau_DABA

Reputation: 2528

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

Upvotes: 10

user319198
user319198

Reputation:

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;

Upvotes: 220

Related Questions