How to Create Temp Table in SQL Server

In this SQL Server tutorial, we will learn about the Temp table in SQL Server, Types of Temp tables in SQL Server, How to create a Temp table in SQL Server, and will cover the following topics.

SQL Server Temp Table
SQL Server Create Temp Table
SQL Server Create Temp Table From Select
SQL Server Create Temp Table in Stored procedure
SQL Server Create Temp Table if not exists
SQL Server Create Temp Table and insert values
SQL Server Create Temp Table with autoincrement / identity column
SQL Server Create Temp Table with index
SQL Server Create Temp Table with collation
SQL Server Create Temp Table from list of values
SQL Server Create Temp Table in function

Here we are using sql server 2019 and sql server management studio.

SQL Server Temp Table

As the name implies, a temporary table in SQL Server is a database table that exists only temporarily on the database server.
Temporary tables are either dropped automatically when the session that created them ends, or they can be deliberately dropped by users.
Temporary tables are handy for holding immediate result sets that need to be accessed multiple times.
In many ways, temporary tables can mimic physical tables, giving us more freedom.
In SQL Server, the temporary tables are stored in a system database called “tempdb“.

Temporary tables in SQL Server are divided into two categories: local temporary tables and global temporary tables. These are explained in the below paragraphs.

Local Temporary Table– The scope of a local temporary table is limited to the connection in which they are constructed. In other words, the local temporary tables remain visible and accessible for the duration of the connection. Once the connection is closed the tables will be automatically deleted (dropped).
Global Temporary Table– Global Temporary Tables are visible to all connections and are dropped after the last connection that refers to the table closes. A Unique Table Name is required for a Global Table Name.

Read: SQL Server Row_Number – Complete tutorial

SQL Server Create Temp Table

Now in this section, we will understand how to create temporary tables in SQL Server. As we already discussed in the previous topic about two types of temporary tables available in SQL Server. So in this section, we will discuss the method through which we can create local as well as global temporary tables in SQL Server.

Creating Local Temporary Tables

The local temporary tables in SQL Server can be created by using CREATE TABLE statement. But we also need to add a single hashtag (#) sign at the starting of the table name to declare it as a local temporary table. And the name of the table will be appended with random numbers.

For this implementation, we can follow the following syntax.

CREATE TABLE #table_name (
      column_1 datatype,
      column_2 datatype
)

Now, for example, consider the following code given below used to create a local temporary table named “SampleTempTable”.

CREATE TABLE #SampleTempTable (
    ID int PRIMARY KEY IDENTITY(1,1),
    FirstName varchar(255),
    LastName varchar(255),
    City varchar(255)
)

After successful query execution, the table will be created and it will appear under the “Temporary Tables” directory in the “tempdb” database.

SQL Server Create Temp Table — Local Temp Table

Read SQL Server logical operators

Creating Global Temporary Tables

The global temporary tables in SQL Server can also be created by using CREATE TABLE statement. But we need to add double hashtag (##) signs instead of single (#) at the starting of the table. And there will be no random numbers appended to the table name.

For this implementation, we can follow the following syntax.

CREATE TABLE ##table_name (
      column_1 datatype,
      column_2 datatype
)

Now, for example, consider the following code given below used to create a global temporary table named “SampleTempTable”.

CREATE TABLE ##SampleTempTable (
    ID int PRIMARY KEY IDENTITY(1,1),
	FirstName varchar(255),
    LastName varchar(255),
    City varchar(255)
)

And after creation, the table will also appear under the “Temporary Tables” directory in the “tempdb” database.

Read: IDENTITY_INSERT in SQL Server

SQL Server Create Temp Table From Select

Till now, we have discussed only one method of creating a temporary table in SQL Server that is by using CREATE Table statement.
Now, in this section, we will discuss an alternative method to create a temporary table which is by using the SELECT INTO statement.
The SELECT INTO statement in SQL Server is used to copy one table to another, either with or without data.

Now for creating a temporary table in SQL Server by using SELECT INTO statement, we can follow the following syntax.

SELECT select_columns,.. INTO #temporary_table
FROM old_table

By using the above syntax, we can copy any table data to a temporary table. For example, consider the following query given below.

SELECT Name, Department INTO #temp_emp ---temporary table
FROM EmployeeTable
WHERE ID > 4;

In the above example, we have created a temporary table named “#temp_emp” with two-column taken from a table named “EmployeeTable“. The above statement will build a temporary table and filled it with data from the “EmployeeTable” table.

Read: SQL Server Add Column with examples

SQL Server Create Temp Table and insert values

Till this point, we have learned how we can create temporary tables in SQL Server. Now in this section, we will discuss different methods to insert values into a temporary table.

There are 2 ways through which we can insert values into a temporary table in SQL server. The first is by using the INSERT INTO statement, just like we use in the case of permanent tables. And the second method is to use the SELECT INTO statement to copy data from one table to another.

Using INSERT INTO Statement

Now for this implementation, we can follow the following syntax.

INSERT INTO #temp_table Values (value_1, value_2, value_3);

So after creating a temporary table in SQL Server, first we have to use the INSERT INTO statement followed by name of the temporary table. And in the last, we have to use the VALUES clause to specify the values to be inserted.

For demonstration, consider the following code given below.

-- Create Local temporary table    
CREATE TABLE #SampleTempTable (id INT , Name VARCHAR(20), Gender VARCHAR(10)) 

--Insert data into Temporary Tables    
INSERT INTO #SampleTempTable Values (101,'Pauly','Male');    
INSERT INTO #SampleTempTable Values (102,'Bili','Male');    
INSERT INTO #SampleTempTable Values (103,'Lynea','Female');

In the above example, first, we are using the CREATE TABLE statement to create a local temporary table. After this, we are using the INSERT INTO statement to insert values into the table.

And after successful query execution, if query the temp table we will get the following data.

inserting values into temp table in sql server — Inserting Values to Temp table

Using SELECT INTO Statement

Now for this implementation, we can follow the following syntax.

SELECT select_columns,.. INTO #temp_table
FROM old_table

Now by using the above syntax, we can copy any table data to a temporary table. For example, consider the following query given below.

SELECT Name, Department INTO #temp_emp
FROM EmployeeTable WHERE ID > 5;

In the above example, we have created a temporary table named “#temp_emp” with two-column taken from a table named “EmployeeTable“. After this, we are using the WHERE clause to filter out results where id is greater than 5.

After successful query execution, a new temp table will be created with the following data.

SQL Server Create Temp Table and insert values — Final Result

SQL Server Create Temp Table in Stored procedure

In SQL Server, we can even create a temporary table using stored procedures in SQL server 2019. But the temporary tables will be automatically deleted (dropped) once the connection in which it is created is closed.

In this section, we will understand how we can create local as well as global temporary tables using stored procedures in SQL Server.

Creating Local Temp Table

Now, if we create a local temporary table using the stored procedure, the temporary table will automatically be deleted or dropped once the execution of the stored procedure is completed.

And for creating a stored procedure to create a local temporary table, we can follow the following syntax.

CREATE PROCEDURE procedure_name  
AS  
BEGIN  
CREATE TABLE #table_name(column_1 datatype, column_2 datatype,...)
END

In the above syntax, we are using the CREATE PROCEDURE statement to create a stored procedure. After this, we are using the CREATE TABLE command to create a table. And to create a local temp table, we have to use a single hash symbol (#) at the starting of the table name.

Now let’s understand the implementation with the help of an example, and for this, consider the following query.

CREATE PROCEDURE Sp_localTmpTable  
AS  
BEGIN  
--Creating Local Temp Table
CREATE TABLE #tmpTable(Id int, Name nvarchar(20))  

--Inserting values to temp table  
Insert into #tmpTable Values(101, 'Bob')  
Insert into #tmpTable Values(102, 'Ross')  
Insert into #tmpTable Values(103, 'Kane')  

--Listing table values  
Select * from #tmpTable  
End

In the example, we are creating a procedure with the “Sp_localTmpTable” name. And in the procedure, first, we are creating a local temp table named “tmpTable“. After this, we are inserting three records in the temporary table. In the end, we are listing all the records using the SELECT statement.

Now if we execute the procedure, we will get the following output.

Creating local temp table using stored procedure in sql server — Final Result

Creating Global Temp Table

Now to create a global temporary table in SQL Server, we have to use double hash symbols (##) at the starting of the table name. And for this, we can follow the following syntax.

CREATE PROCEDURE procedure_name
AS
BEGIN
CREATE TABLE ##table_name(column_1 datatype, column_2 datatype,...)
END

Now let’s understand the implementation with the help of an example, and for this, consider the following query.

CREATE PROCEDURE Sp_globalTmpTable  
AS  
BEGIN  
--Creating Local Temp Table
CREATE TABLE ##tmpTable(Id int, Name nvarchar(20))  

--Inserting values to temp table  
Insert into ##tmpTable Values(101, 'Bob')  
Insert into ##tmpTable Values(102, 'Ross')  
Insert into ##tmpTable Values(103, 'Kane')  

--Listing table values 
Select * from ##tmpTable  
End

Again in the example, we are creating a procedure with the “Sp_globalTmpTable” name. And in the procedure, first, we are creating a global temp table named “tmpTable“. After this, we are inserting three records in the temporary table. In the end, we are listing all the records using the SELECT statement.

Now if we execute the procedure, we will get the following output.

Creating global temp table using stored procedure — Result

Read: SQL Server drop table if exists

SQL Server Create Temp Table if not exists

It is important to assign a unique table name while creating a table in SQL Server, else the server will return an error. And same is the case while creating a temporary table in SQL Server.

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

Now for this implementation, we can follow the following syntax given below.

IF object_id('tempdb..#table_name') is NULL
BEGIN
  CREATE TABLE #table_name (
     Col1 datatype
   , Col2 datatype
  );
END
ELSE
Print 'Table already exists'

In the above syntax, we are using the IF ELSE block to check whether a table name exists or not. And if it doesn’t exist, we are using the CREATE TABLE statement to create a temporary table. And if the table name already exists, then the query will return ‘Table already exists’ as a result.

Now let’s understand this implementation with the help of the following query given below.

IF object_id('tempdb..#tmpTable') is null
BEGIN
  CREATE TABLE #tmpTable (
     ID int, NAME varchar(10)
  );
END
ELSE
Print 'Table already exists'

In the above example, we are checking if “#tmpTable” already exists or not in SQL Server. And the query will return “Table already exists” if the table name is already there in SQL Server, else it will create the table.

SQL Server Create Temp Table with autoincrement/identity column

An autoincrement column in SQL Server generates unique numbers automatically whenever a new record is inserted into the table. And this is usually the primary key field that we want to be set automatically whenever a new record is inserted.

Now to create an autoincrement field, we have to use the IDENTITY property in SQL Server. Also, autoincrement column in SQL Server is referred to as 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.

Here is the syntax of IDENTITY property that we use in SQL Server.

IDENTITY [( seed, increment)]

The IDENTITY property basically carries two 2 arguments given below.

Seed– It is used to specify the starting value of the column, which will be automatically added by the SQL Server. It is set to 1 by default.
Increment– It is used to define the incremental value that will be added to the identity value of the previous record. It is also set to 1 by default.

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

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

By using the above syntax, we are creating an autoincrement field that will start from 1, and it will increment the value by 1 for every new record.

Now to demonstrate, consider the following code given below, used to create a temporary table with three columns, out of which one is an identity column.

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

And after creation, the table will appear under the “Temporary Tables” directory in the “tempdb” database.

SQL Server Create Temp Table with index

In SQL Server, indexes are used to fetch or retrieve table data more quickly. The SQL Server indexes are identical to indexes at the end of books, which are used to rapidly locate a topic. And SQL Server supports 2 types of indexes.

Clustered Index- A clustered index in SQL Server is used to define the order in which data is physically stored in a table. Because table data can only be sorted in one direction, each table can only have one clustered index.
Non-Clustered Index- A non-clustered index does not physically arrange the data in sorted order in memory. And a non-clustered index is maintained in one location while table data is stored in another. This allows each table to have multiple non-clustered indexes.

In SQL Server, we can add clustered as well as non-clustered indexes to a temporary table. And in this section, we will understand the implementation of both with the help of examples.

Clustered Index

Now to create a temporary table with clustered index we can follow the following syntax.

--creating clustered index  
CREATE CLUSTERED INDEX index_name ON #tmp_table(column,..)

After successfully creating and inserting data to a temporary table, we can use the above syntax to create a clustered index. And for this, we have to specify the columns and their order to index them.

Now for demonstration, consider the following query, used to create a clustered index for the Employee table. And arranging names in a table in ascending order.

CREATE CLUSTERED INDEX IDX_Employees ON #Employee(Name ASC)

And now, if we query the temporary table after creating a clustered index, we will get the following output.

Creating temp table with clustered index

Non-Clustered Index

Now to create a temporary table with non-clustered index we can follow the following syntax.

--creating non-clustured index  
CREATE INDEX index_name ON #tmp_table(column,..)

For example, consider the following query, used to create a non-clustered index for the Employee table. And the names in a table are arranged in descending order.

CREATE INDEX IDX_Employee ON #Employee(Name DESC)

Moreover, data is kept in the desired order inside the non-clustered index. The index contains the index’s column values as well as the address of the record that the column value corresponds to.

Due to this additional step in non clustered index, it is slower than the clustered index.

Read: Exception Handling in SQL Server

SQL Server Create Temp Table with collation

In SQL Server, collations are used to define sorting rules, case sensitivity, and accent sensitivity attributes for the data. Character data types, such as char and varchar, have collations that specify the code page and characters that can be represented for that data type.

Now to get the default collation used by our SQL Server instance we can use the following query.

SELECT SERVERPROPERTY ('Collation')

After executing the above query, we will get “SQL_Latin1_General_CP1_CI_AS” as an output, which is the default collation name used by SQL Server.

default collaction used by sql server — Default Collaction used by SQL Server

But we can use some other collation for our data, and for this, we can use the COLLATE clause in SQL Server. The COLLATE clause is used to define the collation property that a column should follow.

And to use the COLLATE clause while creating a temporary table in SQL Server, we can follow the following syntax.

CREATE TABLE #table_name(
	column_name character_datatype COLLATE collation_name
	)

Now for demonstration, consider the following example given below.

CREATE TABLE #tmpTable (
	ID INT,
	Name VARCHAR(20)  COLLATE SQL_Latin1_General_CP1_CS_AS,
	Gender VARCHAR(8)
	)

In the above example, we are creating a local temporary table in SQL Server with the “#tmpTable” name. For the temporary table, we have defined 3 columns, and for the Name column, we are using the COLLATE clause to change the character property from case-insensitive to the case-sensitive character.

SQL Server Create Temp Table from list of values

In SQL Server, we can also create a temporary table from a list of available values in another table. For this implementation, we have to use the SELECT INTO statement, which is used to copy data from one table to another in SQL Server.

Now to use the SELECT INTO statement in SQL Server, we can follow the following syntax.

SELECT select_columns,.. INTO #temp_table
FROM old_table

For better understanding, consider the following example given below, used to create a local temp table from a persistent table in SQL Server.

SELECT id, Name, Department INTO #tempTable
FROM EmployeeTable

In the above example, we are creating a local temporary table named “#tempTable“. And we are cloning 3 columns with their values from EmployeeTable to the temporary table.

Now if we query the newly created temporary table, we will get the following result.

SQL Server Create Temp Table in function

A temporary table in SQL Server is mostly used to store intermediate results and to use that intermediate results in your application efficiently. The temporary table will automatically be deleted once the connection in which it is created is closed.

Now, what if we can use a function to create and store values in a temporary table and directly use the function to execute tasks. But SQL Server doesn’t support the creation of temporary tables within a function.

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

The SQL Server will raise an error if we try to create a temporary table in SQL server within a function.