Recipe 4.1. Cloning a Table

Problem

You need into create a table that has exactly the same structure as an existing table.

Solution

Use CREATE TABLE ... LIKE to clone the table structure. If it's also necessary to copy some or all of the rows from the original table to the new one, use INSERT INTO ... SELECT.

Discussion

When you need to create a new table that is just like an existing table, use this statement:

CREATE TABLE new_table LIKE original_table; 

The structure of the new table will be exactly the same as that of the original table, with a few exceptions: CREATE TABLE ... LIKE does not copy foreign key definitions, and it doesn't copy any DATA DIRECTORY or INDEX DIRECTORY table options that the table might use.

The new table will be empty. If you also need to copy the rows from the original table to the new table, use an INSERT INTO ... SELECT statement:

INSERT INTO new_table SELECT * FROM original_table; 

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 mail table named mail2 and populate it with the rows only for mail sent by barb:

mysql> CREATE TABLE mail2 LIKE mail; mysql> INSERT INTO mail2 SELECT * FROM mail WHERE srcuser = 'barb'; mysql> SELECT * FROM mail2; +---------------------+---------+---------+---------+---------+-------+ | t                   | srcuser | srchost | dstuser | dsthost | size  | +---------------------+---------+---------+---------+---------+-------+ | 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    | 58274 | | 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |   271 | | 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   | 98151 | +---------------------+---------+---------+---------+---------+-------+ 

For more information about INSERT ... SELECT, see Section 4.2.