DECLARE LOCAL TEMPORARY TABLE statement

Declares a local temporary table.

Syntax

DECLARE LOCAL TEMPORARY TABLE table-name
( { column-definition [ column-constraint ... ] | table-constraint | pctfree | like-clause }, ... )
like-clause | as-clause
[ ON COMMIT { DELETE | PRESERVE } ROWS
   | NOT TRANSACTIONAL ]

pctfree : PCTFREE percent-free-space

percent-free-space : integer

like-option :
{ INCLUDING | EXCLUDING } option [ ,... ]

option :
{ IDENTITY 
 | DEFAULTS 
 | CONSTRAINTS 
 | INDEXES 
 | PRIMARY KEY
 | FOREIGN KEYS
 | COMMENTS
 | STORAGE
 | ALL }

as-clause :
[ (column-name, ... ) ] AS ( select-statement )

Parameters

LIKE clause
The default for like-option is to exclude (EXCLUDING) all options. If source-table is a view, then like-option is ignored if it is not applicable.

Specify INCLUDE to include any of the following column attributes from the original table in the new table:
- IDENTITY Include default autoincrement information.
- DEFAULTS Include default value expressions, excluding autoincrement.
- CONSTRAINTS Include column and table check and unique constraints.
- INDEXES Include indexes, excluding primary and foreign keys.
- PRIMARY KEY Include the primary key.
- FOREIGN KEY Include foreign keys.
- COMMENTS Include comments on columns.
- STORAGE Include the compression setting.
AS clause Use this clause to clone a table based on a SELECT statement.
ON COMMIT clause
By default, the rows of a temporary table are deleted on a COMMIT. You can use the ON COMMIT clause to preserve rows on a COMMIT.
NOT TRANSACTIONAL clause
A table created using this clause is not affected by either COMMIT or ROLLBACK. The NOT TRANSACTIONAL clause provides performance improvements in some circumstances because operations on non-transactional temporary tables do not cause entries to be made in the rollback log. For example, NOT TRANSACTIONAL can be useful if procedures that use the temporary table are called repeatedly with no intervening COMMITs or ROLLBACKs.

Remarks

You cannot use the REFERENCES column-constraint or the FOREIGN KEY table-constraint on a local temporary table.

The DECLARE LOCAL TEMPORARY TABLE statement declares a temporary table.

The DECLARE LOCAL TEMPORARY TABLE...LIKE syntax declares a new table based directly on the definitions of another table. You can also clone a table with additional columns, constraints, and LIKE clauses, or create a table based on a SELECT statement.

Tables created using DECLARE LOCAL TEMPORARY TABLE do not appear in the SYSTABLE view of the system catalog.

The rows of a declared temporary table are deleted when the table is explicitly dropped or when the table goes out of scope. You can also explicitly delete rows using TRUNCATE or DELETE.

Declared local temporary tables within compound statements exist within the compound statement. Otherwise, the declared local temporary table exists until the end of the connection.

Two local temporary tables within the same scope cannot have the same name. If you create temporary table with the same name as a base table, the base table only becomes visible within the connection once the scope of the local temporary table ends. A connection cannot create a base table with the same name as an existing temporary table.

If you want a procedure to create a local temporary table that persists after the procedure completes, use the CREATE LOCAL TEMPORARY TABLE statement instead.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
DECLARE LOCAL TEMPORARY TABLE is part of optional Language Feature F531. The PCTFREE and NOT TRANSACTIONAL clauses are not in the standard. The column and constraint definitions defined by the statement may also include extension syntax that is not in the standard. In the ANSI/ISO SQL Standard, tables created via the DECLARE LOCAL TEMPORARY TABLE statement appear in the system catalog; however, this is not the case in the software.
Transact-SQL
DECLARE LOCAL TEMPORARY TABLE is not supported by Adaptive Server Enterprise. In SAP Adaptive Server Enterprise, one creates a temporary table using the CREATE TABLE statement with a table name that begins with the special character #.

Example

The following example illustrates how to declare a temporary table in a stored procedure:

BEGIN
  DECLARE LOCAL TEMPORARY TABLE TempTab ( number INT );
  ...
END

Example

The second statement in the following example creates a table, myT2, and sets the data type of its column, myColumn, to the data type of the last_name column in myT1 using a %TYPE attribute. Since additional attributes such as nullability are not applied, myT2.myColumn does not have the same NOT NULL restriction that myT1.last_name has.

CREATE TABLE myT1
( first_name   CHAR(20),
   last_name   VARCHAR NOT NULL );

CREATE TABLE myT2
( myColumn myT1.last_name%TYPE );

The following example declares a local temporary table and then declares a second local temporary table based on the first table's definitions.

DECLARE LOCAL TEMPORARY TABLE table1 ( ID INT NOT NULL DEFAULT AUTOINCREMENT, NAME LONG VARCHAR ) ;
DECLARE LOCAL TEMPORARY TABLE table2 ( ADDRESS LONG VARCHAR ) ;

The following statement declares a local temporary table just like table1 with no data:

DECLARE LOCAL TEMPORARY TABLE table3 LIKE table1 INCLUDING IDENTITY ;

The following statement declares a local temporary table like table1, but with additional columns:

DECLARE LOCAL TEMPORARY TABLE table4 ( LIKE table1 INCLUDING IDENTITY, LIKE table2, phone LONG VARCHAR );

The following statement declares a local temporary table with any data that is in table1 and '555-5555' in the phone column for each row:

DECLARE LOCAL TEMPORARY TABLE table5 AS ( SELECT * , '555-5555' AS phone FROM table1 ) WITH DATA ;