SQL Clone Tables

Ratings:
(4)
Views:795
Banner-Img
  • Share this blog:

SQL Clone Tables Overview

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. If you are using MySQL RDBMS, you can handle this situation by following steps.

  • Use SHOW CREATE TABLE command 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 by Step Instructions on Cloning Tables in SQL

Try out following example to create a clone table for TUTORIALS_TBL whose structure is as follows: Step 1: Get complete structure about table SQL> SHOW CREATE TABLE TUTORIALS_TBL \G; *************************** 1. row *************************** Table: TUTORIALS_TBL 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`) ) TYPE=MyISAM 1 row in set (0.00 sec) Step 2: Rename this table and create another table SQL> 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; Query OK, 0 rows affected (1.80 sec) Step 3: After executing step 2 you will 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. SQL> INSERT INTO CLONE_TBL (tutorial_id, ->                         tutorial_title, ->                         tutorial_author, ->                         submission_date) -> SELECT tutorial_id,tutorial_title, ->        tutorial_author,submission_date, -> FROM TUTORIALS_TBL; Query OK, 3 rows affected (0.07 sec) Records: 3            Duplicates: 0  Warnings: 0 Finally you will have exact clone table as you wanted to have.

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.


Stay Updated


Get stories of change makers and innovators from the startup ecosystem in your inbox