Temporary Table in SQL

Overview: In this tutorial, you will know how to create Temporary table in SQL, types of temporary tables in SQL and how to use temporary tables effectively.

What is Temporary table in SQL?

Temporary tables in SQL are those tables which exist temporarily in SQL. The temporary tables in SQL are useful for store and process interim result which can be used any time. Temporary tables are very useful when we need to store temporary data.

In SQL Server Temporary table start with hash symbol “#”.

Types of Temporary tables in SQL?

There are two types of Temporary tables in SQL 

• Local Temporary table start with “#“
• Global Temporary table start with “##“

Syntax: Create Temporary table

CREATE TABLE #Temprory_table (
             Column1 datatype1,
             Column2 datatype2
             );

Lets take few example, the following statement creates a temporary table using the CREATE TABLE command

Example #1: Create Temporary Table in SQL

You can create Temp table in SQL by using CREATE TABLE command in SQL, temp table start with hash symbol “#”

CREATE TABLE #Employee_Temp_Details (
             Employee_ID INT NOT NULL,
             Employee_name VARCHAR(100),
             DOJ Date,
             );

The above command create temp table in SQL with name Employee_Temp_Details. if you want to see temp table you can use simple SELECT command as shown below code

SELECT * FROM #Employee_Temp_Details

Output will look like this

Only you will see the structure of table but not the data as we do not enter any data, however you can use INSERT INTO command to insert data in table

Example #2: Create Temporary tables using SELECT INTO statement

You can also create temporary table use SELECT INTO command and The name of the temporary table starts with a hash symbol “#”

SELECT  
    [order ID]
    Sales
INTO #Temp_Order   // temporary table
FROM
   Orders_Tbl

In above example its create #Temp_Order tabel with two column from Orders_Tbl by using SELECT command. The above statement created the temporary table and populated data from the Orders_Tbl table into the temporary table #Temp_Order

Example #3: Dropping Temporary Tables

Remove Temp Table Automatically:

When you close the SQL server connection then SQL Server always drop a temporary table automatically and also drop global temporary table once the connection that created it closed a.

DROP Manual Temporary Tables:

You can remove the temporary table manually by using the DROP TABLE statement

DROP TABLE ##table_name;

Second option you can use to check if drop table exist or not as shown below code

DROP TABLE IF EXISTS #table_name

Note: recommended to use IF EXIST command to drop table

Example #4: Create Global Temporary tables

To create a Global Temporary Table you need to put double hash symbol “##” in front of table name. Global temporary table will also use CREATE TABLE command to create temporary table in SQL as shown below image

CREATE TABLE ##Employee_Temp_Details (
             Employee_ID INT NOT NULL,
             Employee_name VARCHAR(100),
             );

Conclusion:

Temporary tables in SQL are those tables which exist temporarily in SQL. This is The temporary tables in SQL are useful for store and process interim result which can be used any time. Temporary tables are very useful when we need to store temporary data.