MySQL Table Creation Cloning an existing table
Example
A table can be replicated as follows:
CREATE TABLE ClonedPersons LIKE Persons;
The new table will have exactly the same structure as the original table, including indexes and column attributes.
As well as manually creating a table, it is also possible to create table by selecting data from another table:
CREATE TABLE ClonedPersons SELECT * FROM Persons;
You can use any of the normal features of a SELECT statement to modify the data as you go:
CREATE TABLE ModifiedPersons
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;
Primary keys and indexes will not be preserved when creating tables from SELECT. You must redeclare them:
CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID))
SELECT PersonID, FirstName + LastName AS FullName FROM Persons
WHERE LastName IS NOT NULL;
