I-M-JM I-M-JM - 4 months ago 19
SQL Question

copy table from one database to another, maintaining table's structure and renaming it too

I want to copy a table (say tbl_1) from one database (say source_db) to another database (say target_db), with following things in consideration:


  • Structure of table should be preserved, including primary key and
    auto-increment key

  • While creating a copy of tbl_1, I need to rename it to cpy_tbl_1



How it is possible using query?

P.S. I know there will be many similar questions like mine, but I have those special considerations too.

Answer
CREATE TABLE db_target.cloned_table 
SELECT * 
FROM db_source.source_table;

With the previous sentence the table will be created with just the fields and it's types, but no keys, constraints, engine will be set. You can specify them manually in the same sentence like this:

CREATE TABLE db_target.cloned_table (
  a INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (a), KEY(b)
) ENGINE=MyISAM 
SELECT b,c FROM db_source.source_table;

mysql create table doc