aspose file tools*
The moose likes JDBC and the fly likes How to copy a table from one Schema to another Schema table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to copy a table from one Schema to another Schema table " Watch "How to copy a table from one Schema to another Schema table " New topic
Author

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

Subhash Pavuskar
Ranch Hand

Joined: Jun 29, 2011
Posts: 57

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

Joined: Dec 08, 2010
Posts: 1510
    
    5

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.


Regards,
Anayonkar Shivalkar (SCJP, SCWCD, OCMJD, OCEEJBD)
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39834
    
  28
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
Ranch Hand

Joined: Apr 01, 2011
Posts: 81

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 Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

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
Bartender

Joined: Dec 08, 2010
Posts: 1510
    
    5

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
Ranch Hand

Joined: Jun 29, 2011
Posts: 57

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 Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to copy a table from one Schema to another Schema table