![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]
The following example illustrates how to declare a local temporary table in Embedded SQL:
EXEC SQL DECLARE LOCAL TEMPORARY TABLE MyTable ( number INT );
The following example 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 only visible 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 get 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.
None.
None.
