CREATE TABLE - Based on an existing table

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
 LIKE table
|[IGNORE|REPLACE] [AS] SELECT...

Explanation

These two syntaxes for the CREATE TABLE statement allow a new table to be created based on an existing table. With the LIKE clause, a table is created based on the structure of the existing table given.

Examples

For example, suppose a database has a table called employees that contains information on full-time and part-time employees. Suppose further that it has been decided that information on part-time employees should be stored in a separate table. You could execute the following statement to create a new table for part-time employees with the same structure as the existing employees table:

CREATE TABLE part_time_employees
LIKE employees;

This statement results in a new table with the same structure, but without any data. If the table that was copied has a primary key or any indexes, they won't be copied. You can use the CREATE INDEX statement to create an index. You would first have to do the following to copy the data over:

INSERT INTO part_time_employees 
SELECT * FROM employees 
WHERE part_time = 'Y';

To create a new table based on the structure of an existing table and to copy some data from the old table to the new one, you can enter something like the following statement:

CREATE TABLE part_time_employees
SELECT *
FROM employees
WHERE part_time = 'Y';

CREATE INDEX emp_id ON part_time_employees(emp_id);

In this example, the table structure is copied and the data is copied for rows where the part_time column has a value of Y, meaning yes. You could follow this statement with a DELETE statement to delete the rows for part-time employees from the employees table. The second SQL statement in the example above restores the index on emp_id. However, it doesn't make the column a primary key or an AUTO_INCREMENT one. For that you would need to use ALTER TABLE instead.

You can use the IGNORE keyword before the SELECT statement to instruct MySQL to ignore any error messages regarding duplicate rows, to not insert them, and to proceed with the remaining rows of the SELECT statement. Use the REPLACE keyword instead if duplicate rows are to be replaced in the new table.