SQL | Declare Local Temporary Table

Declare Local Temporary Table statement used to create a temporary table. A temporary table is where the rows in it are visible only to the connection that created the table and inserted the rows.

Syntax –

DECLARE LOCAL TEMPORARY TABLE table-name
( column-name [ column-value ] );

Example :

DECLARE LOCAL TEMPORARY TABLE TempGeek ( number INT );

INSERT INTO Geeks 
VALUES (1), (2), (3), (4);

Select * 
from TempGeek; 

Once you create a local temporary table, you cannot create another temporary table of that name for as long as the temporary table exists.

Example –
You can create a local temporary table by entering :

declare local temporary table Geektable

If you then try to select into “Geektable” or declare Geektable again, you receive an error indicating that Geektable already exists.

When you declare a temporary table, exclude the owner specification.

If you specify identical owner.table in addition to DECLARE LOCAL TEMPORARY TABLE statement within the same session, a syntax error is reported.

Example –

DECLARE LOCAL TEMPORARY TABLE user1.Gfgt(col1 int);
DECLARE LOCAL TEMPORARY TABLE user.Gfgt(col1 int);

The error “Item Gfgt already exists” is reported :

You can, however, create a temporary table with identical name as an existing base table or global temporary table, however local temporary tables are used first.

Example –
Consider this sequence :

CREATE TABLE Geeks (num int);
INSERT INTO Geeks VALUES (9), (8) ;

DECLARE LOCAL TEMPORARY TABLE Geeks (num int);

INSERT INTO Geeks VALUES (6), (7);

SELECT * 
FROM Geeks; 

Output :
The result returned is

Any reference to Geeks refers to the local temporary table Geeks until the local temporary table is dropped by the connection.

ALTER TABLE and DROP INDEX statements cannot be used on local temporary tables.