Name

Synopsis

Inputs

TEMPORARY or TEMP

If specified, the table is created only within this
session, and is automatically dropped on session exit.
Existing permanent tables with the same name are not
visible (in this session) while the temporary table
exists. Any indexes created on a temporary table are
automatically temporary as well.

table

The name of the new table to be created. This table
must not already exist. However, a temporary table can be
created that has the same name as an existing permanent
table.

column

The name of a column. Multiple column names can be
specified using a comma-delimited list of column names.
If column names are not provided, they are taken from the
output column names of the SELECT query.

select_clause

A valid query statement. Refer to SELECT for a description of the
allowed syntax.

Outputs

Description

CREATE TABLE AS creates a table and
fills it with data computed by a SELECT
command. The table columns have the names and datatypes
associated with the output columns of the SELECT (except that you can override the SELECT column names by giving an explicit list of
column names).

CREATE TABLE AS bears some resemblance
to creating a view, but it is really quite different: it creates
a new table and evaluates the SELECT just
once to fill the new table initially. The new table will not
track subsequent changes to the source tables of the SELECT. In contrast, a view re-evaluates the given
SELECT whenever queried.

This command is functionally equivalent to SELECT INTO, but it is preferred since
it is less likely to be confused with other uses of the SELECT ... INTO syntax.