DECLARE LOCAL TEMPORARY TABLE statement

DECLARE LOCAL TEMPORARY TABLE 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

For definitions of column-definition, column-constraint, table-constraint, and pctfree, see CREATE TABLE 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.

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.

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. See Using compound statements.

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. See CREATE LOCAL TEMPORARY TABLE statement.

None.

SQL/2008 DECLARE 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 DECLARE LOCAL TEMPORARY TABLE statement appear in the system catalog; this is not the case with SQL Anywhere.
Transact-SQL DECLARE 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 '#'. See CREATE TABLE statement.

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

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

DECLARE LOCAL TEMPORARY TABLE statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example