How to copy a table from one Schema to another Schema table

[Subhash Pavuskar]

How to copy a table from one Schema to another Schema table In MySql ?

[Anayonkar Shivalkar]

Subhash Pavuskar wrote:How to copy a table from one Schema to another Schema table In MySql ?

I'm not sure how this question is related to Java, but you can do it with below query:

CREATE TABLE new_db.table_name LIKE old_db.table_name;
INSERT INTO new_db.table_name SELECT * FROM old_db.table_name;

First query would create a new table exactly like original table(i.e. retaining keys, indexes etc.) - just make sure that the table with same name does not exist in new db etc.
Second query would copy the data from original table to new table.

I hope this helps.

[Campbell Ritchie]

Anayonkar Shivalkar wrote: . . . I'm not sure how this question is related to Java, . . .

Agree. I shall move it to our databases forum

I hope this helps.

It does, yes. Surely it will throw an error if the table already exists, since you didn’t say IF NOT EXISTS

[vinayak jog]

SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE statement that creates the given table. The statement requires the SELECT privilege for the table. As of MySQL 5.0.1, this statement also works with views.
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id INT(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM

insert into [tablename] select * from [schema.tablename]

you can proceed in this direction

[Wendy L Gibbons]

Subhash Pavuskar wrote:How to copy a table from one Schema to another Schema table In MySql ?

are you trying to do this in a java program?

[Anayonkar Shivalkar]

Campbell Ritchie wrote:It does, yes. Surely it will throw an error if the table already exists, since you didn’t say IF NOT EXISTS

Thanks for pointing it out.

[Subhash Pavuskar]

Thanks For your response guys !!!
@Wendy Gibbons : No i wanna Try it out through terminal like to get back up of table we use command Ex " mysqldump -u admin -p admin --no-data accounts > accounts.sql"
so is that possible to copy the table from one schema to another using terminal ?

[Wendy L Gibbons]

Subhash Pavuskar wrote:Thanks For your response guys !!!
@Wendy Gibbons : No i wanna Try it out through terminal like to get back up of table we use command Ex " mysqldump -u admin -p admin --no-data accounts > accounts.sql"
so is that possible to copy the table from one schema to another using terminal ?

This question has already been answered, Anayonkar Shivalkar supplied you with the SQL required.

or you could use backup dumps like you yourself suggested, it is only a text file so you could edit it to change the schema/database name if those were included in the file.