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; 

number
1
2
3
4



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) ;

num
9
8

DECLARE LOCAL TEMPORARY TABLE Geeks (num int);

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

num
6
7

SELECT * 
FROM Geeks; 

Output :
The result returned is

num
6
7

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.