How to Create Temp Table in SQL Server

Last week, my team required me to create a temporary table to store data for a short period of time. I created a Local temporary table and a global temporary table to store specific details for a specific time.

Temporary tables will be temporarily available in the SQL Server. Once the connection is lost, the temporary table will be deleted immediately. It will be helpful to store temporary data in the SQL Server.

In this tutorial, I will explain how to create a Temporary table in SQL Server.

Table of Contents

What is a Temporary Table in SQL Server

A temporary table, also known as a temp table, stores temporary data for a short time. It is useful for storing the immediate result that can be accessed multiple times.

Temporary table used to perform complex calculations. Temporary tables are used in different database systems such as MySQL, PostgreSQL, Oracle, SQL Server, and others, though the syntax and features may vary slightly between implementations.

Create a Temporary Table in the SQL Server

To create a temporary table in SQL Server, we should use the word Temporary or the temp keyword before the table name. You can also use # before the table name.

Create Table #OrderDetails (
Order Id int,
Order Name varchar(20)
);

Insert into  #OrderDetails values (01, 'Battery'),
                                  (02, 'Keyboard');

This is how we can create a temporary table in SQL Server. You can Drop a temporary table by DROP Table #OrderDetails.

Types of Temporary Tables in SQL Server

There are two types of Temporary tables in SQL Server, namely,

Local Temporary Table
Global Temporary Table

Local Temporary Table

Local temporary tables are visible only to the connection that creates them and are deleted when the connection is closed. A single # is used to create a temporary table. If the Temporary Table is created inside the stored procedure, it will be deleted automatically upon the completion of the stored procedure execution.

Global Temporary Table

Global temporary tables are visible to all connections of SQLServer and are only lost when the last connection referencing the table is closed. To create a temporary global table, we must add ‘##’ before the table name. The connection will be lost

Create a Temporary Table using SELECT into Approach

In this example, you must create a temporary table from the existing one. I’m taking the EmployeeId and EmployeeRole columns from an EmployeeInfo table. The current EmployeeInfo table has the below data.

Now, we have to create a temporary table (From EmployeeInfo) for all the field

select EmployeeId, EmployeeRole
into #EmpInfo
from EmployeeInfo
where EmployeeId >2;

select * from #EmpInfo

By doing this, EmployeeInfo fields will be added to the temporary table #EmpInfo.

Create a Global Temporary Table

We can also create a Global temporary table by using a double slash ## before the temporary table name.

Now, we can use the Create Table approach to create a global

CREATE Table ##Temp_table (
EmployeeId int primary key,
EmployeeName nvarchar(50),
EmployeeRole varchar (50)
)

Insert into  ##Temp_table (EmployeeId, EmployeeName, EmployeeRole)
select EmployeeId, Employeename, EmployeeRole
from EmployeeInfo
Where Employeesalary>55000

select * from ##Temp_table

Create a Temporary Table in SQL if it does not Exist

It is essential to create a unique table name while creating a table in SQL Server, else the server will return an error. It is essential to create a unique table

So, in this section, we will first understand how to check if the name assigned while creating a new temporary table already exists. And if the table name doesn’t exist already, then the query will create a new temp table with that name.

The syntax is given below,

IF object_id('tempdb..#table_name') is NULL
BEGIN
  CREATE TABLE #table_name (
     Column1 datatype
   , Column2 datatype
  );
END
ELSE
Print 'There is already table in Database'

We use the IF ELSE block in the above syntax to check whether a table name exists. We will use the CREATE TABLE statement to create a temporary table if it doesn’t exist. If the table name already exists, the query will return ‘Table already exists’ as a result.

Let’s understand this with the help of the following query below.


IF object_id('tempdb..#tmpTable') is null
BEGIN
  CREATE TABLE ##TempTable (
     ID int, NAME varchar(10)
  );
END
ELSE
Print 'There is already table in Database'

Create Temporary Table in the Function

If we want to use a function to create and store values in a temporary table, we can directly execute tasks using the function. However, SQL Server doesn’t support the creation of temporary tables within a function.

A simple solution for this implementation is to store data in table variables instead of temporary tables within a function.

Create Function TempTableinfunction()
Returns INT
AS
BEGIN
Create Table #TempTable (Name VARCHAR(50))
             
End

Create a Temporary Table with Auto-increment Column

An auto-increment column in SQL generates the number automatically when a new record is inserted into the table. This will be the primary key for the table.

We must use the IDENTITY property in SQL Server to create an autoincrement field. The autoincrement column in SQL Server is also called the Identity column. The server automatically generates the value in an identity column. In most cases, a user cannot add a value to an identity column.

Below is the syntax for Identity used in SQL Server

IDENTITY [( seed, increment)]

It will have two arguments, namely,

Seed – It specifies the column’s starting value, which the SQL Server will automatically add. It is set to 1 by default.
Increment – It is used to increment the value added to the identity value.

Now, we will create a temporary table in SQL Server with an autoincrement or identity column. We can follow the syntax below.

CREATE TABLE #table_name (
       column_1 INT IDENTITY(1,1), 
       column_2 datatype,
       )

For example, we will see a table named tmp Table. Here, I have given the ID identity (1,1). While inserting it, I did not add any values for the ID. It will automatically take the values.

CREATE TABLE #tmpTable (
       ID INT IDENTITY(1,1), 
       Name varchar(50),
       Gender varchar(10)
       )

	   Insert into #tmptable values ('Peter', ' Male'),
	                                ('Daisy', 'Female')

		select * from #tmpTable

Conclusion

In this SQL Server tutorial, we have learned about the Temp table in SQL Server, the Types of Temp tables in SQL Server, and how to create a Temp table in SQL Server. I hope this tutorial has answered your questions.