![DECLARE CURSOR statement [T-SQL]](arrow-left.gif){kind=icon}
Declares a local temporary table.
DECLARE LOCAL TEMPORARY TABLE table-name
… ( column-definition [ column-constraint ] …
[ , column-definition [ column-constraint ] … ]
[ , table-constraint ] … )
…[ ON COMMIT { DELETE | PRESERVE } ROWS
NOT TRANSACTIONAL]
Illustrates how to declare a local temporary table in Embedded SQL:
EXEC SQL DECLARE LOCAL TEMPORARY TABLE MyTable ( number INT );
Illustrates how to declare a local temporary table in a stored procedure:
BEGIN
DECLARE LOCAL TEMPORARY TABLE TempTab (
number INT
);
...
END
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 can create a local temporary table implicitly by entering:
select * into #tmp from table1
Or you can 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 these 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);
An attempt to create a base table or a global temporary table will fail, if a local temporary table of the same name exists on that connection, as the new table cannot be uniquely identified by owner.table.
You can, however, create a local temporary table with the same name as an existing base table or global temporary table. References to the table name access the local temporary table, as local temporary tables are resolved first.
For example, consider this sequence:
CREATE TABLE t1 (c1 int); INSERT t1 VALUES (9); DECLARE LOCAL TEMPORARY TABLE t1 (c1 int); INSERT t1 VALUES (8); SELECT * FROM t1;
The result returned is 8. Any reference to t1 refers to the local temporary table t1 until the local temporary table is dropped by the connection.
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.
None
None
