Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Subhash Pavuskar
Ranch Hand
Posts: 57
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How to copy a table from one Schema to another Schema table In MySql ?
 
Anayonkar Shivalkar
Bartender
Posts: 1557
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 48917
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 83
MySQL Database Netbeans IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1557
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 57
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic