CREATE TABLE AS v14

Name

CREATE TABLE AS — Define a new table from the results of a query.

Synopsis

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

Description

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT. You can override the column names by providing an explicit list of new column names.

CREATE TABLE AS is similar to creating a view. However, it differs because it creates a table and evaluates the query once to fill the new table initially. The new table doesn't track later changes to the source tables of the query. In contrast, a view reevaluates its defining SELECT statement whenever you query it.

Parameters

GLOBAL TEMPORARY

Specify to create the table as a temporary table. For details, see CREATE TABLE.

table_name

The name (optionally schema-qualified) of the table to create.

column_name

The name of a column in the new table. If you don't provide column names, the names are taken from the output column names of the query.

query

A query statement (a SELECT command). For a description of the allowed syntax, see SELECT.