The SQL CREATE TABLE ... AS SELECT statement enables you to insert the results of a query into a new table.

Basic Example

Here’s a basic example to demonstrate selecting and inserting the data into a new table.

CREATE TABLE Pets2 AS
(SELECT * FROM Pets);

This creates a new table called Pets2 (with the same columns as Pets), and inserts the query results into it. However, it doesn’t include indexes and other column attributes.

The SQL standard requires parentheses around the subquery clause, but they may be optional in your DBMS (for example PostgreSQL).

If we select both tables, we can see they both have the same data.

barney=# SELECT * FROM Pets;
 petid | pettypeid | ownerid | petname |    dob     
-------+-----------+---------+---------+------------
     1 |         2 |       3 | Fluffy  | 2020-11-20
     2 |         3 |       3 | Fetch   | 2019-08-16
     3 |         2 |       2 | Scratch | 2018-10-01
     4 |         3 |       3 | Wag     | 2020-03-15
     5 |         1 |       1 | Tweet   | 2020-11-28
     6 |         3 |       4 | Fluffy  | 2020-09-17
     7 |         3 |       2 | Bark    | 
     8 |         2 |       4 | Meow    | 
(8 rows)

barney=# SELECT * FROM Pets2;
 petid | pettypeid | ownerid | petname |    dob     
-------+-----------+---------+---------+------------
     1 |         2 |       3 | Fluffy  | 2020-11-20
     2 |         3 |       3 | Fetch   | 2019-08-16
     3 |         2 |       2 | Scratch | 2018-10-01
     4 |         3 |       3 | Wag     | 2020-03-15
     5 |         1 |       1 | Tweet   | 2020-11-28
     6 |         3 |       4 | Fluffy  | 2020-09-17
     7 |         3 |       2 | Bark    | 
     8 |         2 |       4 | Meow    | 
(8 rows)

When the Table Already Exists

If we try to run the CREATE TABLE ... AS SELECT statement again, we get an error, due to the table already existing.

CREATE TABLE Pets2 AS
(SELECT * FROM Pets);

Result:

relation "pets2" already exists

If you want to insert data into a table that already exists, use the INSERT INTO... SELECT statement. This will append the data to any existing data. That is, it will add new rows to the table, while keeping any existing rows.

Filtering the Results

The SELECT statement can do the usual SELECT statement stuff, such as filtering the results with a WHERE clause.

CREATE TABLE Pets3 AS
(SELECT * FROM Pets
WHERE DOB < '2020-06-01');

In this example, I filter the data to just those pets who have a date of birth (DOB) from before the 1st of June, 2020.

Selecting from Multiple Tables

You can select data from multiple tables, then have the destination table’s definition be based on the result set.

CREATE TABLE PetsTypesOwners AS
(SELECT
    p.PetId,
    p.PetName,
    p.DOB,
    pt.PetTypeId,
    pt.PetType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId);

Here, we query three tables and insert the results into a table called PetsTypesOwners.

Note that I listed out each column here because I didn’t want to include all columns.

Specifically, I didn’t want to double up on the foreign key/primary key columns. In my case, the foreign keys share the same names as their primary key counterparts in the parent table, and I would have received an error due to duplicate column names being created in the destination table.

Here’s what I mean.

CREATE TABLE PetsTypesOwners2 AS
(SELECT *
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId);

Result:

column "pettypeid" specified more than once

If your foreign keys use different column names to the primary keys, then you’d probably end up with a destination table that contains unnecessary columns (one for the primary key, one for the foreign key, and each containing the same values).

If you really want to include such duplicate columns, but they share the same name, you can always use aliases to assign them with a different name in the destination table.

CREATE TABLE PetsTypesOwners2 AS
(SELECT 
    p.PetId, 
    p.OwnerId AS PetOwnerId, 
    p.PetTypeId AS PetPetTypeId,
    p.PetName,
    p.DOB,
    pt.PetTypeId,
    pt.PetType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId);

In this case I used column aliases to reassign the name of two columns to PetOwnerId and PetPetTypeId.

DBMS Support

Although the CREATE TABLE ... AS SELECT statement conforms to the SQL standard, it is not supported by all DBMSs. Also, for those that do support it, there are variations around its implementation.

Therefore, I’d suggest checking with your DBMS’s documentation if you want to use this statement.

If you use SQL Server, you can use the SELECT INTO statement, which does basically the same thing.

There’s also the INSERT INTO ... SELECT statement that many DBMSs support. This statement inserts the query results into an existing table.