What is a Temporary Table in SQL?
Answers (2)Add Answer
Temporary Tables are the same as Permanent Tables in SQL.
Temporary Tables are Created in TempDB and deleted automatically as soon as the connection is terminated.
It is very useful when storing temporary data and processing intermediate results.
There are 2 types of Temporary Tables.
- Local Temporary Table
- Global Temporary Table
A Local Temporary Table is only available for the session that has created it and automatically deleted/dropped when the connection is closed, that has created it. Single “#” is used as the prefix of a table name to create a Local Temporary Table.
Global Temporary Tables are visible to all connections and they will be deleted/dropped when the last connection is closed which is referencing the table. Global Temporary Table Name must have a Unique Table Name. need to add the “##” symbol before the table name to create a Global Temporary Table.
For a more detailed example, you can refer to the below article.
Temporary Tables help us to store and process intermediate results. Temporary tables are very useful when we need to store temporary data.
//create temp table using # CREATE TABLE #TempTable (id INT, name VARCHAR(25)) //insert data in it INSERT INTO #TempTable VALUES (01, 'TheCodeHubs') //select value from temp table select * from #TempTable
There are two types of Temporary Table
1. Local Temporary Table
- Create Local Temporary Table Single # is used as the prefix of a table name.
- It is automatically deleted(dropped) when the connection that has been established has closed.
2. Global Temporary Table
- Create Local Temporary Table Single ## is used as the prefix of a table name.
- It is automatically deleted(dropped) when the last connection referencing the table is closed.
//create temp table using # CREATE TABLE ##TempTable (id INT, name VARCHAR(25))