ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL Clone Table

sqltuts

The SQL clone table may be used in a situation that 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 steps:

You can handle this situation by following steps:

 

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 exact clone table.

 

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

 

Step 1:

Get complete structure about table.

  1. mysql> SHOW CREATE TABLE tutorials_tbl \G;
  2.        Table: tutorials_tbl
  3. Create Table: CREATE TABLE `tutorials_table` (
  4.   `tutorial_id` int(11) NOT NULL auto_increment,
  5.   `tutorial_Fname` varchar(10);
  6.   `tutorial_Lname` varchar(40) NOT NULL default '',
  7.   `submission_FEE` date default NULL,
  8.   PRIMARY KEY  (`tutorial_id`),
  9.   UNIQUE KEY `Person_Name` (`Person_Name `)
  10. ) TYPE=MINE
  11. 1 row in set (0.00 sec)
  12.  
  13. ERROR:
  14. No query specified

Step 2:

Rename this table and create another table.

  1. mysql> CREATE TABLE `clone_table` (
  2.   -> `tutorial_id` int(11) NOT NULL auto_increment,
  3.     tutorial_id` int(11) NOT NULL auto_increment,
  4.   `tutorial_Fname` varchar(10);
  5.   `tutorial_Lname` varchar(40) NOT NULL default '',
  6.   ` submission_FEE` date default NULL,
  7.  
  8.  -> PRIMARY KEY  (`tutorial_id`),
  9.  UNIQUE KEY `Person_Name` (`Person_Name `)
  10. -> ) TYPE=MINE;
  11. Query OK, 0 rows affected (1.80 sec)

Step 3:

  1. mysql> INSERT INTO clone_tbl (tutorial_id,
  2.     ->                        tutorial_Fname,
  3.     ->                        tutorial_Lname,
  4.     ->                        submission_FEE
  5.     -> SELECT tutorial_id,tutorial_Fname,
  6.     ->        tutorial_Lname,submission_fee,
  7.     -> FROM tutorials_table;
  8. Query OK, 3 rows affected (0.07 sec)
  9. Records: 3  Duplicates: 0  Warnings: 0

 

You will have exact clone table as the user wished for.

Facebook Comments

Check Your Domain Ranking

5 Comments

Add a Comment
  1. For the reason that the admin of this web page is working, no doubt very shortly it will be famous, due to its quality contents.

  2. magnificent publish, very informative. I wonder why the opposite specialists of this sector don’t notice this. You must continue your writing. I am confident, you’ve a huge readers’ base already!

  3. Nos actualizamos tanto como el sector nos ofrece, estamos pendientes de las ultimos novedades de productos para Sex Shop de este modo logramos estar en los sitios punteros y ser referente de contar con todas las novedades.

  4. Unas mujeres prefieren sostener las piernas abiertas muy abiertas y otras las mantienen sutilmente cerradas.

  5. I have read so many articles on the topic of the blogger lovers except this post is genuinely a good post, keep it up.

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!