DECLARE LOCAL TEMPORARY TABLE statement

Description

Declares a local temporary table.

Syntax

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

Examples

Example 1

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

Usage

The DECLARE LOCAL TEMPORARY TABLE statement declares a temporary table.

A local temporary table and the rows in it are visible only to the connection that created the table and inserted the rows. By default, the rows of a temporary table are deleted on COMMIT.

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 CREATE TABLE statement for definitions of column-definition, column-constraint, and table-constraint, and the NOT TRANSACTIONAL clause. See SELECT statement for an example of how to select data into a temporary table.

Once you create a local temporary table, either implicitly or explicitly, you cannot create another temporary table of that name for as long as the temporary table exists. For example, you could create a local temporary table implicitly by entering:

select * into #tmp from table1

Or, you could create a local temporary table explicitly by declaring it:

declare local temporary table foo

If you then try to select into #tmp or foo, or declare #tmp or foo again, you receive an error indicating that #tmp or foo already exists.

When you declare a local temporary table, omit the owner specification. If you specify the same owner.table in more than one DECLARE LOCAL TEMPORARY TABLE statement in the same session, a syntax error is reported. For example, an error is reported when the following statements are executed in the same session:

DECLARE LOCAL TEMPORARY TABLE user1.temp(col1 int);
DECLARE LOCAL TEMPORARY TABLE user1.temp(col1 int);

If the owner name is omitted, then the error “Item temp already exists” is reported:

DECLARE LOCAL TEMPORARY TABLE temp(col1 int);
DECLARE LOCAL TEMPORARY TABLE temp(col1 int);

You cannot use the ALTER TABLE and DROP INDEX statements on local temporary tables.

You cannot use the sp_iqindex, sp_iqtablesize, and sp_iqindexsize stored procedures on local temporary tables.


Side effects

None.

Standards

Permissions

None.

See also

CREATE TABLE statement

SELECT statement