CREATE TEMPORARY TABLE

The CREATE TEMPORARY TABLE statement defines a temporary table for the current connection.

This statement is similar to the DECLARE GLOBAL TEMPORARY TABLE statements, but uses different syntax to provide compatibility with external business intelligence tools.

For general information and notes about using temporary tables, see the Using Temporary Tables topic in our Developer’s Guide.

Splice Machine does not currently support creating temporary tables stored as external tables.

Syntax

CREATE [LOCAL | GLOBAL] TEMPORARY TABLE table-Name {
      ( {column-definition | Table-level constraint}
         [ , {column-definition} ] * )
      ( column-name [ , column-name ] * )
  }
  [NOLOGGING | ON COMMIT PRESERVE ROWS];

Splice Machine generates a warning if you attempt to specify any other modifiers other than the NOLOGGING and ON COMMIT PRESERVE ROWS modifiers shown above.

LOCAL | GLOBAL

These values are ignored by Splice Machine, and are in place simply to provide compatibility with external tools that use this syntax.

table-Name

Names the temporary table.

Table-level constraint

A constraint that is applied to this table, as described in the Constraints clause topic.

column-definition

Specifies a column definition. See column-definition for more information.

You cannot use generated-column-spec in column-definitions for temporary tables.

column-name

A SQL Identifier that names a column in the table.

NOLOGGING

If you specify this, operations against the temporary table will not be logged; otherwise, logging will take place as usual.

ON COMMIT PRESERVE ROWS

Specifies that the data in the temporary table is to be preserved until the session terminates.

Restrictions on Temporary Tables

You can use temporary tables just like you do permanently defined database tables, with several important exceptions and restrictions that are noted in this section.

Operational Limitations

Temporary tables have the following operational limitations:

  • exist only while a user session is alive
  • cannot be altered using the  RENAME COLUMN statements
  • do not get backed up
  • cannot be used as data providers to views
  • cannot be referenced by foreign keys in other tables
  • are not displayed by the  SHOW TABLES command

Also note that temporary tables persist across transactions in a session and are automatically dropped when a session terminates.

Table Persistence

Here are two important notes about temporary table persistence. Temporary tables:

  • persist across transactions in a session
  • are automatically dropped when a session terminates or expires

Examples

splice> CREATE GLOBAL TEMPORARY TABLE FirstAndLast(
      id INT NOT NULL PRIMARY KEY,
      firstName VARCHAR(8) NOT NULL,
      lastName VARCHAR(10) NOT NULL )
   ON COMMIT PRESERVE ROWS;
0 rows inserted/updated/deleted

See Also