Temporary Tables are most likely as Permanent Tables. Temporary Tables are Created in TempDB and are automatically deleted as soon as the last connection is terminated. Temporary Tables helps us to store and process intermediate results. Temporary tables are very useful when we need to store temporary data. The Syntax to create a Temporary Table is given below:
To Create Temporary Table:
CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))
To Insert Values Into Temporary Table:
INSERT INTO #EmpDetails VALUES (01, 'Lalit'), (02, 'Atharva')
To Select Values from Temporary Table:
SELECT * FROM #EmpDetails
There are 2 types of Temporary Tables: Local Temporary Table, and Global Temporary Table. These are explained as following below.
- Local Temporary Table:
A Local Temp Table is available only for the session that has created it. It is automatically dropped (deleted) when the connection that has created it, is closed. To create Local Temporary Table Single “#” is used as the prefix of a table name.
Also, the user can drop this temporary table by using the “DROP TABLE #EmpDetails” query. There will be Random Numbers are appended to the Name of Table Name. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution.
CREATE PROCEDURE ProcTemp AS BEGIN CREATE TABLE #EmpDetails INSERT INTO #EmpDetails VALUES ( 01, 'Lalit'), ( 02, 'Atharva') SELECT * FROM #EmpDetails END EXECUTE ProcTemp
- Global Temporary Table:
To create a Global Temporary Table, add the “##” symbol before the table name.
CREATE TABLE ##EmpDetails (id INT, name VARCHAR(25))
Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name. There will be no random Numbers suffixed at the end of the Table Name.
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.
- SQL | Declare Local Temporary Table
- 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
- 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
- How to print duplicate rows in a table?
- How to find Nth highest salary from a table
- PHP | MySQL ( Creating Table )
- Check if Table, View, Trigger, etc present in Oracle
- MySQL | Recursive CTE (Common Table Expressions)
- Query to find 2nd largest value in a column in Table
- Altering a table to add a collection data type in Cassandra
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. 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.