CREATE LOCAL TEMPORARY TABLE statement

Use this statement within a procedure to create a local temporary table that persists after the procedure completes and until it is either explicitly dropped, or until the connection terminates.

Syntax

CREATE 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

Parameters

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

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

Permissions

None.

Side effects

None.

Standards and compatibility

SQL/2003 SQL/foundation feature outside of core SQL.

Example

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

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

Send feedback about this page via email or DocCommentXchange