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 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.

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.

SQL/2008 CREATE LOCAL TEMPORARY TABLE is part of optional language feature F531 of the SQL/2008 standard. The PCTFREE and NOT TRANSACTIONAL clauses are vendor extensions. The column and constraint definitions defined by the statement may also include vendor extension syntax. In SQL/2008, the standard stipulates that tables created via the CREATE LOCAL TEMPORARY TABLE statement appear in the system catalog; this is not the case with SQL Anywhere.
Transact-SQL CREATE LOCAL TEMPORARY TABLE is not supported by Adaptive Server Enterprise. In Sybase Adaptive Server Enterprise, one creates a temporary table using the CREATE TABLE statement with a table name that begins with the special character #.

Example

Discuss this page in DocCommentXchange.