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.
DECLARE LOCAL TEMPORARY TABLE table-name ( column-name [ column-value ] );
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.
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.
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.
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;
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.
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.
- What is Temporary Table in SQL?
- Select into and temporary tables in MS SQL Server
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | Create Table Extension
- How to Get the names of the table in SQL
- SQL | Checking Existing Constraints on a Table using Data Dictionaries
- SQL query to find unique column values from table
- SQL | Query to select NAME from table using different options
- DUAL table in SQL
- Table operations in MS SQL Server
- Table Variable in SQL Server
- Check whether a Table exists in SQL Server database or not
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- Local Indexing and Materialized views in Cassandra 3.0
- Local as View (LAV)
- How to print duplicate rows in a table?
- How to find Nth highest salary from a table
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.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.