Overview of SQL Server Temporary Table

Introduction

The SQL Server Temporary table, which is also known as SQL temp table, is useful when processing data, especially during transformation where the intermediate results are transient. Here in this, we will explore about SQL server temp table.

Getting Started

SQL temp table is used to store data temporarily, it supports the CRUD (Create, Read, Update, and Delete) like a persistent database table. The temporary table in SQL drops automatically when the session that created the SQL Temp table closes because temp tables are only visible to the session in which they were created, or it can be dropped manually using SQL Query.

MSSQL temp tables behave or acts like regular table, the same syntax of query that uses for regular is used to perform CRUD operation in temp tables but the measure difference is that it provides t temporary storage to store data.

In the dedicated SQL pool resource, the SQL Server temp table offers a performance benefit because their results are written to local rather than remote storage.

Temporary tables are stored inside the Temporary Folder of TempDB. Whenever we create a temporary table, it goes to the Temporary folder of the tempdb database. It can be seen by expanding the temporary tables folder inside the tempdb database.

Types of Temporary Tables

There are two types of temporary table in SQL, these are given below:

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables

Local temporary tables are the tables stored in tempdb. It is created by prefixing your table name with a #. This SQL temp table is only available to the session that created it. temp table is automatically destroyed at the termination of the procedure or session.

Global Temporary Tables

Global temporary tables are available to all the sessions and all the users, it gets destroyed or dropped when the last session using the temp table is completed.

The situations where temporary tables are used in SQL

1. You may need a lookup table for several queries that are used in creating a report. This lookup table doesn’t normally exist - and may be relatively expensive to build as a subquery every time you need it - but it makes your report queries run faster and are easier and cleaner to code once it does exist.

2. You may need temporary staging tables for reports that visit several databases. At the moment, I have a bunch of reports that visit about several dozen database instances of several different types. Initial queries run on each db, fetch data into temporary tables in the reporting db, and the final report query runs on these temporary tables. This “reporting structure” would be very hard to code without using temporary tables.

3. Similar to the above, you may temporarily need data from one database in order to run a query in a second database. To do this, it’s often easiest to dump the data from the first db, load it to a temp table in the second db, and then run the final query in the second db.

Create Temp Table in SQL Server

To create a temporary table, the SQL server provides the same query syntax as a regular table but the difference is that you need to use a single # as a prefix with the table name for the local temp table and a double ## for the global temp table. Like the below example.

Local Tamp Table

 create table #local_temp_table  
 (  
 Column1 varchar(20),  
 Column2 int  
 )  

Global Temporary Table

 create table ##global_temp_table  
 (  
 Column1 varchar(20),  
 Column2 int  
 )  

From existing table

  -- local temp table   
  SELECT ID, ProductName, CreateOn INTO #TempTable_Product   
 FROM products   
  --global temp table   
  SELECT ID, ProductName, CreateOn INTO ##TempTable_Product   
 FROM products   

Insert to SQL Server temp table.

The INSERT TO statement is used to insert row into the SQL server temp table. Let's say we have a with three columns like given image and the insert statement will be like:

Overview of SQL Server Temporary Table

 -- local temp table  
 INSERT INTO #TempTable_Product (ID, ProductName,CreateOn)   
 --global temp table  
 INSERT INTO ##TempTable_Product (ID, ProductName,CreateOn)   

From Existing Table

 -- local temp table  
 INSERT INTO #TempTable_Product (ID, ProductName,CreateOn)  
 SELECT id, name ,date  
 FROM products  
 --global temp table  
 INSERT INTO ##TempTable_Product (ID, ProductName,CreateOn)  
 SELECT id, name ,date  
 FROM products  

Fetch the row from the temporary table

 -- local temp table  
 SELECT ID, ProductName, CreateOn FROM #TempTable_Product  
 --global temp table  
 SELECT ID, ProductName, CreateOn FROM ##TempTable_Product  

Update a row in SQL Server temporary table

 -- local temp table  
 UPDATE #TempTable_Product SET ProductName='Product1 WHERE ID=1  
 --global temp table  
 UPDATE ##TempTable_Product SET ProductName='Product1 WHERE ID=1  

Delete a row from the temporary table

 -- local temp table  
 DELETE FROM #TempTable_Product  
 --global temp table  
 SELECT  FROM ##TempTable_Product  

Drop a SQL Server Temporary Table

To DROP SQL Server temp table, the same DDL query which is used for the regular table is used.

 -- local temp table  
 DROP TABLE #TempTable_Product  
 --global temp table  
 DROP TABLE ##TempTable_Product  

Summary

Here in this blog post, we explored temporary tables in SQL and how to create temporary tables in an SQL server. I hope you enjoyed it.

Thanks