How to copy mysql table data to another table

Last updated on February 8th, 2022 at 04:50 pm

It is sometimes required for the developers to have a clone/copy of their production tables in order to have a backup or for some calculation purpose. Here is an example of how to clone/copy the data from one mysql table to another using INSERT INTO and SELECT statement.

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)