• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 57
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How to copy a table from one Schema to another Schema table In MySql ?
 
Bartender
Posts: 1558
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 83
Netbeans IDE MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1558
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Did Steve tell you that? Fuh - Steve. Just look at this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic