Creates a table based on the results of a query.

Syntax

CREATE [ GLOBAL TEMPORARY ] TABLE table_name
  [ (column_name [, ...] ) ]
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
  [ TABLESPACE tablespace ]
  AS query

Description

The CREATE TABLE AS command creates a table and fills it with data computed by a SELECT command. The table columns have the same names and data types as the output columns of the SELECT command. However, you can override the column names by specifying an explicit list of new column names.

The CREATE TABLE AS command is similar to creating a view. However, unlike creating a view, the CREATE TABLE AS command creates a new table and evaluates the query only once to fill the new table. The new table does not track subsequent changes to the source tables of the query. In contrast, a view evaluates its defining SELECT statement whenever it is queried.

Parameters

ParameterDescription
Parameter Description
GLOBAL TEMPORARY If this parameter is specified, the table is created as a temporary table. For more information, see the CREATE TABLE command.
table_name The name of the table to be created. The name can be schema-qualified.
column_name The name of a column to be created in the new table. If no column names are specified, the names of columns in the query result are used.
query A query statement. It is also a SELECT command. For more information about the supported syntax, see the SELECT command.