CLONE TABLE in SQL

CLONE TABLE in SQL is used in situations where you only want to make an exact copy or copy of an existing table to check or perform something without affecting the original table.

If you are using MySQL RDBMS, you can do this by following the steps below:

• Use the SHOW CREATE TABLE command to receive a CREATE TABLE statement that identifies the source table, index, and all related items.
• Modify the above statement and change the table name of the Clone table and execute that command. This way you will have an exact copy table.
• Optionally, if you need the table content to be copied, use the INSERT INTO or SELECT statement.

Examples illustrating how to use CLONE TABLE

For example, create a replication table for TEST_TBL with the following structure:

Step 1: Create a complete structured 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 doing step 2, if you want to copy data from the old table, use the 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 a clone table as you like.

In the next section, we will learn about Copy Processing - DUPLICATE in SQL , remember to follow up.

Previous article: TEMPORARY TABLE temporary table in SQL

Next lesson: Handling copy - HANDLING DUPLICATE in SQL