Working With Clone Table in MySQL Server

In MySQL server when we want to need an accurate copy of a table result set, and after that we have to create a table select doesn't complete our target because the copy of this table must be included in the same indexes with default values. We have to hold this situation by the using these steps.

First of all we have to use show create table to get a create table statement and that defines the source tables structure, indexes. 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.

For Example
The following example to create a clone table for tutorial.

Step 1

Create Table
mysql> SHOW CREATE TABLE tutorial  \G ;

Step 2

Rename this table and create another table:

CREATE TABLE clone
(
tutorial_id int(11) NOT NULL auto_increment,
tutorial_title varchar(100) NOT NULL default '',
tutorial_autho varchar(40) NOT NULL default '',
submission_date date default NULL,
PRIMARY KEY (tutorial_id),
UNIQUE KEY AUTHOR_INDEX (tutorial_author)
);




Step 3

In this step we will a clone a table in a database. If you want to a copy data from an old table then you can do it by using INSERT INTO... SELECT statement.

Syntax
INSERT INTO clone
(tutorial_id,tutorial_title, tutorial_author, submission_date)
VALUES
(1,"Learn PHP", "John Poul", NOW());

INSERT INTO clone
(tutorial_id,tutorial_title, tutorial_author, submission_date)
VALUES
(2,"Learn MySQL", "Abdul S", NOW());

INSERT INTO clone
(tutorial_id,tutorial_title, tutorial_author, submission_date)
VALUES
(3,"JAVA Tutorial", "Sanjay", '2007-05-06');

mysql> SELECT tutorial_id,tutorial_title,tutorial_author,submission_date
      >  FROM clone;



Finally we will have an exact clone table as we wanted to have.

To copy only part of the table, add an appropriate WHERE clause that identifies which rows to copy.

For example
These statements create a copy of the clone table named clone1 and populate it with the rows only for clone sent by Learn MySQL.

Resources 

• Approximate Joins in MySQL Server
• Modifying and Updating Tables in MySQL
• Creating table and retrieving data from MySQl in JDBC
• How to Handle Null Values in MYSQL Server
• DROP in MySQL