INTO TABLE clause

Creates a table from a query result set.

Creates a table from a query result set.

Syntax

Permanent table:

INTO [TABLE] [[{namespace. | database. }]schema.]table

Temporary table:

INTO [scope] TEMP[ORARY] [TABLE] [[database.]schema.]table
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]

Parameters

scope
Specifies visibility of a temporary table definition:

  • GLOBAL (default): The table definition is visible to all sessions, and persists until you explicitly drop the table.

  • LOCAL: The table definition is visible only to the session in which it is created, and is dropped when the session ends.

Regardless of this setting, retention of temporary table data is set by the keywords ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS (see below).

For more information, see Creating temporary tables.

{ namespace. | database. }
Name of the database or namespace that contains table:
  • Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
  • Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

You cannot specify both a database and namespace name.

schema
Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.
table
The name of the table to create.
ON COMMIT { DELETE | PRESERVE } ROWS
Specifies whether data is transaction- or session-scoped:

  • DELETE (default) marks the temporary table for transaction-scoped data. Vertica removes all table data after each commit.

  • PRESERVE marks the temporary table for session-scoped data, which is preserved beyond the lifetime of a single transaction. Vertica removes all table data when the session ends.

Examples

The following SELECT statement has an INTO TABLE clause that creates table newTable from customer_dimension:

=> SELECT * INTO TABLE newTable FROM customer_dimension;

The following SELECT statement creates temporary table newTempTable. By default, temporary tables are created at a global scope, so its definition is visible to other sessions and persists until it is explicitly dropped. No customer_dimension data is copied into the new table, and Vertica issues a warning accordingly:

=> SELECT * INTO TEMP TABLE newTempTable FROM customer_dimension;
WARNING 4102:  No rows are inserted into table "public"."newTempTable" because
  ON COMMIT DELETE ROWS is the default for create temporary table
HINT:  Use "ON COMMIT PRESERVE ROWS" to preserve the data in temporary table
CREATE TABLE

The following SELECT statement creates local temporary table newTempTableLocal. This table is visible only to the session in which it was created, and is automatically dropped when the session ends. The INTO TABLE clause includes ON COMMIT PRESERVE ROWS, so Vertica copies all selection data into the new table:

=> SELECT * INTO LOCAL TEMP TABLE newTempTableLocal ON COMMIT PRESERVE ROWS
     FROM customer_dimension;
CREATE TABLE