Last updated on November 14th, 2024 at 01:12 pm

Developers may occasionally need to create a duplicate of their production tables to ensure a backup or for specific calculation purposes. Below is an example illustrating how to duplicate data from one MySQL table to another by utilizing the INSERT INTO and SELECT statements.

Assuming both tables have the same column names. First create a table with the name ‘TUTORIALS_TABLE_CLONE’ . This is the table which has the backup or clone data

CREATE TABLE `TUTORIALS_TABLE_CLONE` (
`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`);
)


Create another table with the name ‘TUTORIALS_TABLE’. This is the main table.

CREATE TABLE `TUTORIALS_TABLE` (
  `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`)
);

Insert some values to the main table

INSERT INTO TUTORIALS_TABLE (tutorial_id,
tutorial_title,
tutorial_author,
submission_date)
VALUES ("1", "PHP Load Test ","Admin","2013-04-05");

Now here is the query to clone the data.

INSERT INTO TUTORIALS_TABLE_CLONE (tutorial_id,tutorial_title,tutorial_author,submission_date) SELECT tutorial_id,tutorial_title,tutorial_author,submission_date FROM TUTORIALS_TABLE;

Once you run the above query then the data you inserted in TUTORIALS_TABLE should be the same as data in TUTORIALS_TABLE_CLONE

mysql> select * from TUTORIALS_TABLE;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | PHP Load Test  | Admin           | 2013-04-05      |
+-------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> select * from TUTORIALS_TABLE_CLONE;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | PHP Load Test  | Admin           | 2013-04-05      |
+-------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)