HomeProgramSQL

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:

  1. Use the SHOW CREATE TABLE command to receive a CREATE TABLE statement that identifies the source table, index, and all related items.
  2. 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.
  3. 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

4 ★ | 1 Vote
learn sql

You should read it

May be interested

  • Table operations in WordTable operations in Word
    introduce table operations in word. table operations in word 2013 include: 1. draw cells in a table. - creating a table can not ignore merge cell operation, for example, if you want the column of the school day to have 2 values ​​of even and odd dates, make 2 columns into one.
  • Create clone virtual machine for current Windows hard driveCreate clone virtual machine for current Windows hard drive
    the following article will show you how to create a virtual clone for the system and how to use it when you have created a virtual machine.
  • How to fix table errors in Word overflowHow to fix table errors in Word overflow
    error word overflow table can be corrected in 2 different ways and very simple to perform, helping to bring the table in word aligned correctly.
  • Instructions for naming Excel tablesInstructions for naming Excel tables
    tables in excel are named table 1, table2, table 3,... but if you keep the names like this, it will be difficult to handle the table. therefore, users should change and name the excel table.
  • Instructions for inserting table captions in WordInstructions for inserting table captions in Word
    similar to other content in word, you can insert a caption for the table to explain the content of the table or simply enter a name for the table to make it easier for people to follow.
  • What kind of drilling machine is best?What kind of drilling machine is best?
    table drilling machines are very popular among mechanical people. if you still do not know which type of table drill to choose, you can not ignore the 4 products below.
  • Navigate directly from table to table in Word 2013Navigate directly from table to table in Word 2013
    the cursor is not in the table to jump to the next table. place the cursor on any paragraph or table and press f5 (or use ctrl + g) to open the find and replace dialog box. the go to tab of the dialog box is automatically selected. select table in the go to what list> click next.
  • Create automatic table of contents in Word 2003Create automatic table of contents in Word 2003
    creating an automatic table of contents in word 2003 makes the document look more professional, easy to find the item you need by clicking on the table of contents. so how to create a table of contents in word fastest? the answer is to create an automatic table of contents!
  • Clone activity in GitClone activity in Git
    we have an empty repository on the server and tom also pushed his first version. now, jerry can observe his changes. clone operation creates a remote repository instance.
  • MS Access 2003 - Lesson 12: Working with a tableMS Access 2003 - Lesson 12: Working with a table
    you have learned how to create and design tables in access. and now it's time to learn how to manipulate all tables.

SQL