Temporary Tables

Temporary RAM-only tables can be created within a Kognitio session. Such tables are only visible from the session that created them and are dropped when the session ends. The table name cannot be the same as a permanent table in the same schema. Temporary tables are typically used to hold intermediate results of data transformations.

Usage

CREATE [LOCAL | GLOBAL | CURRENT_SESSION]
TEMPORARY TABLE name(column-definitions)
[distribution]
[COMPRESSED]
[PARTITION IMAGE BY ({column-name}, …)]
[FOR | FROM | AS select-statement]
[ON COMMIT DELETE | PRESERVE ROWS]
[AUTHORIZATION user-name]

CREATE TEMP TABLE name(column-definitions)
[distribution]
[COMPRESSED]
[PARTITION IMAGE BY ({column-name}, …)]
[FOR | FROM | AS select-statement]
[ON COMMIT DELETE | PRESERVE ROWS]
[AUTHORIZATION user-name]

Notes

The CREATE TEMPORARY TABLE form of the command is defined in the SQL standard. CREATE TEMP TABLE is provided for compatibility with other database vendors.

Certain elements of the usual syntax for creating tables are invalid – for example, you cannot specify the table distribution as DISK, or specify disk slabs, as the table is RAM only. The table is always associated with the current session.

Authorisation cannot be specified if the table is generated from a SELECT statement.

The default value for ON COMMIT is different for the two forms of the command: CREATE TEMPORARY TABLE deletes the contents when the current transaction completes, whereas CREATE TEMP TABLE preserves them.

The fact that the default ON COMMIT behaviour of the two forms of the command is different means CREATE TEMP and CREATE TEMPORARY cannot be interchanged without modification if the same behaviour is required.