Cloning an existing table

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;