DECLARE GLOBAL TEMPORARY TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The DECLARE GLOBAL TEMPORARY TABLE statement creates a temporary table.
This statement has the following format:
DECLARE GLOBAL TEMPORARY TABLE [SESSION.]table_name
(column_name format {, column_name format})
ON COMMIT PRESERVE ROWS
WITH NORECOVERY [,LOCATION = (location_spec)]
To create a temporary table by selecting data from another table:
DECLARE GLOBAL TEMPORARY TABLE [SESSION.]table_name
(column_name {, column_name})
AS [WITH common_table_expression] subselect
ON COMMIT PRESERVE ROWS
WITH NORECOVERY [,LOCATION = (location_spec)]
SESSION
Allows the creation of permanent and temporary tables with the same name.
If the SESSION schema qualifier is used, then subsequent SQL statements that reference the table must use the SESSION qualifier. When using this syntax, the creation of permanent and temporary tables with the same name is allowed.
If the SESSION schema qualifier is not used, then subsequent SQL statements that reference the table can optionally omit the SESSION qualifier. This feature is useful when writing portable SQL. When using this syntax, the creation of permanent and temporary tables with the same name is not allowed.
A session table is local to the session, which means that two sessions can declare a global temporary table of the same name and they do not conflict with each other.
Note: Syntaxes cannot be mixed in a single session. For example, if the table is declared with SESSION the first time, all declarations must use SESSION.
table_name
Defines the name of the temporary table.
AS subselect
Defines the subselect, as described in
SELECT.
ON COMMIT PRESERVE ROWS
(Required) Retains the contents of a temporary table when a COMMIT statement is issued.
with_clause
Specifies a list of valid WITH clause options, separated by a comma. Valid options are:
NORECOVERY
(Required for Ingres global temporary table.) Suspends logging for the temporary table.
(Optional for Vector global temporary table.) If specified for a Vector table, this option is ignored. Logging cannot be suspended for Vector temporary tables.
LOCATION=(location_name {, location_name} )
(Optional) Specifies the location where the table is created. It must be a default location or a location with usage type WORK.
If more than one location is specified, all columns are horizontally partitioned across the specified locations.
The location_name must exist (created with CREATE LOCATION) and the database must have been extended to the corresponding area. If the location option is omitted, the table is created in the default database location.