DECLARE GLOBAL TEMPORARY TABLE

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

This statement is similar to the CREATE GLOBAL TEMPORARY TABLE and CREATE LOCAL 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.

Syntax

DECLARE GLOBAL TEMPORARY TABLE table-Name
   { column-definition[ , column-definition] * }
       [ON COMMIT PRESERVE ROWS ]
       [NOT LOGGED]

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

table-Name

Names the temporary table.

column-definition

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

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

ON COMMIT PRESERVE ROWS

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

NOT LOGGED

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

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> DECLARE GLOBAL TEMPORARY TABLE FirstAndLast(
      id INT NOT NULL PRIMARY KEY,
      firstName VARCHAR(8) NOT NULL,
      lastName VARCHAR(10) NOT NULL )
   ON COMMIT PRESERVE ROWS
   NOT LOGGED;
0 rows inserted/updated/deleted

See Also