I want to create a duplicate table of my already created table in the same database. Can you explain, how to create duplicate table of an existing table in MySQL?
Answer No: 178
MySQL provides many methods to create duplicate table with its data or without its data. The following is a simple example to create a duplicate table alongwith data of the original table:
CREATE TABLE new_table_name SELECT * FROM old_table_name;
The above query will create a table named ‘new_table_name’ like table ‘old_table_name’ and entire data from old table will be copied to new table. An important point to note is that this query will not create column attributes and indexes as defined in the original table. However, if you also want to have such attributes, this can be done with another easy way. Take a look at the following queries:
CREATE TABLE new_table_name LIKE old_table_name;
This query will create a copy of the original table with all its constraints and attributes. Now, you need to insert entire data from original table to new one table so run the following query:
INSERT INTO new_table_name SELECT * FROM `old_table_name`;