CLONE TABLES

---

MySQL - Clone Tables

There may be a situation when you need an exact copy of a table and CREATE TABLE ... SELECT doesn't suit your purposes because the copy must include the same indexes, default values and so forth.

You can handle this situation by following the steps given below −

• Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.


• Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.


• Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.
  
  

  EXAMPLE :

  
  

  Try out the following example to create a clone table for tutorials_tbl.

  
  

  Step 1 − Get the complete structure about the table.

  Create Table: CREATE TABLE `tutorials_tbl` (
     `tutorial_id` int(11) NOT NULL auto_increment,
     `tutorial_title` varchar(100) NOT NULL default '',
     `tutorial_author` varchar(40) NOT NULL default '',
     `submission_date` date default NULL,
     PRIMARY KEY  (`tutorial_id`),
     UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
  ) 
  

  
  

  Step 2 − Rename this table and create another table.

  
  

  mysql> CREATE TABLE clone_tbl (
     -> tutorial_id int(11) NOT NULL auto_increment,
     -> tutorial_title varchar(100) NOT NULL default '',
     -> tutorial_author varchar(40) NOT NULL default '',
     -> submission_date date default NULL,
     -> PRIMARY KEY  (tutorial_id),
     -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
  -> ) TYPE = MyISAM;
  

  
  

  Step 3 − After executing step 2, you will create a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement.

  
  

  mysql> INSERT INTO clone_tbl (tutorial_id,
     -> tutorial_title,
     -> tutorial_author,
     -> submission_date)
     
     -> SELECT tutorial_id,tutorial_title,
     -> tutorial_author,submission_date
     -> FROM tutorials_tbl;
  

  
  
  

  MySQL TRIGGERS

  MySQL - Triggers

  posted on 2019-11-29 21:44:07 - mysql Tutorials

  
  

  Grant_ Revoke Privilege

  MySQL - Grant_ Revoke Privilege

  posted on 2019-11-26 23:15:04 - mysql Tutorials

  
  

  MySQL Vs SQL

  MySQL Vs SQL

  posted on 2019-11-25 05:02:26 - mysql Tutorials

  
  

  Prompt Examples

  ChatGPT Prompt Examples

  posted on 2023-06-21 22:37:19 - ChatGPT Tutorials

  
  

  Use Cases

  Chat GPT Key Use Cases

  posted on 2023-06-21 21:03:17 - ChatGPT Tutorials

  
  

  Prompt Frameworks

  Prompt Frameworks

  posted on 2023-06-21 19:33:06 - ChatGPT Tutorials