CREATE LOCAL TEMPORARY TABLE statement

Creates a local temporary table within a procedure that persists after the procedure completes and until it is either explicitly dropped, or until the connection terminates.

Syntax

CREATE LOCAL TEMPORARY TABLE [ IF NOT EXISTS ] [owner.]table-name
( { column-definition [ column-constraint ... ] | table-constraint | pctfree }, ... )
[ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ]

pctfree : PCTFREE percent-free-space

percent-free-space : integer

Parameters

IF NOT EXISTS clause
No changes are made if the named table already exists, and an error is not returned.
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
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 may be useful if procedures that use the temporary table are called repeatedly with no intervening COMMITs or ROLLBACKs.

Remarks

In a procedure, use the CREATE LOCAL TEMPORARY TABLE statement, instead of the DECLARE LOCAL TEMPORARY TABLE statement, when you want to create a table that persists after the procedure completes. Local temporary tables created using the CREATE LOCAL TEMPORARY TABLE statement remain until they are either explicitly dropped, or until the connection closes.

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

Local temporary tables created in IF statements using CREATE LOCAL TEMPORARY TABLE also persist after the IF statement completes.

Two local temporary tables within the same scope cannot have the same owner and name. If you create a local temporary table with the same owner and 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 owner and name as an existing temporary table.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
CREATE LOCAL TEMPORARY TABLE is part of optional ANSI/ISO SQL 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 syntax extensions that are not in the standard. In the ANSI/ISO SQL Standard, tables created via the CREATE LOCAL TEMPORARY TABLE statement appear in the system catalog; however, this is not the case in the software.
Transact-SQL
CREATE 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 creates a local temporary table called TempTab:

CREATE LOCAL TEMPORARY TABLE TempTab ( number INT ) 
ON COMMIT PRESERVE ROWS;