Temp table in stored procedure in SQL Server

In this SQL Server tutorial, we will learn how to use and create a temp table in stored procedure in SQL Server. Moreover, we will also illustrate the following set of topics.

• Create temp table in stored procedure in SQL Server
• Create local temp table in stored procedure in SQL Server
• Create global temp table in stored procedure in SQL Server
• Create index on temp table in stored procedure SQL Server
• Create and drop temp table in stored procedure SQL Server
• How to use temp table in stored procedure SQL Server
• SQL Server insert into temp table from stored procedure without creating temp table
• SQL Server insert into temp table from stored procedure with parameters
• SQL Server create temp table from select in stored procedure

Here we are using SQL Server 2019 and SQL Server Management Studio.

Create temp table in stored procedure in SQL Server

As the name indicates, a temp table in SQL Server is a temporary table that exists only temporarily on the database server. Moreover, a temp table can either be deleted automatically when the session ends or can be deleted manually by users.

So, a temp table in SQL Server is handy for holding immediate result sets that need to be accessed multiple times. On the other hand, a stored procedure in SQL Server is handy for executing a bunch of SQL queries together.

However, in this section, we will combine both the concepts and understand how to create a temp table within a stored procedure. Temporary tables in SQL Server are divided into two categories: local temporary tables and global temporary tables.

And we will discuss how to create different types of temp tables within a procedure in SQL Server.

Create local temp table in stored procedure in SQL Server

A local temporary table in SQL Server remains visible and accessible till the duration of the connection. Once the connection is closed the local temp table will be automatically deleted (dropped).

Let’s understand how to create a local temp table in a stored procedure using an example in SQL Server. For this, consider the following SQL query.

USE [sqlserverguides]
GO

CREATE PROC usp_localTempTbl  
AS  
BEGIN
  --Creating local temp table
  CREATE TABLE #Country( Id INT, Country_Name VARCHAR(50) )  
  
  --Inserting data into the local temp table
  INSERT INTO #Country Values(1, 'United States')  
  INSERT INTO #Country Values(2, 'Canada')  
  INSERT INTO #Country Values(3, 'New Zealand')  
  
  --Fetching the data of local temp table
  SELECT * FROM #Country  
END 

In the above example, first, we are creating a stored procedure named usp_localTempTbl. After this, within the procedure, we are creating a local temp table named #Country. Then, we are inserting 3 records with the temp table. In the end, we are using the SELECT statement to fetch the data of the temp table.

USE [sqlserverguides]
GO

EXEC usp_localTempTbl 

Now, when we execute the above procedure, all these operations will take place and the procedure will return a result set.

Create global temp table in stored procedure in SQL Server

A global temp table in SQL Server is visible to all connections and is dropped when the last connection that refers to the table closes. Moreover, a global temp requires a unique table name.

Let’s understand how to create a global temp table in a stored procedure using an example in SQL Server. For this, consider the following SQL query.

USE [sqlserverguides]
GO

CREATE PROC usp_globalTempTbl  
AS  
BEGIN
  --Creating global temp table
  CREATE TABLE ##Country( Id INT, Country_Name VARCHAR(50) )  
  
  --Inserting data into the global temp table
  INSERT INTO ##Country Values(101, 'United States')  
  INSERT INTO ##Country Values(102, 'Canada')  
  INSERT INTO ##Country Values(103, 'New Zealand')  
  
  --Fetching the data of global temp table
  SELECT * FROM ##Country  
END 

In the above example, first, we are creating a stored procedure named usp_globalTempTbl. After this, within the procedure, we are creating a local temp table named ##Country. Then, we are inserting 3 records with the temp table. In the end, we are using the SELECT statement to fetch the data of the global temp table.

USE [sqlserverguides]
GO

EXEC usp_globalTempTbl 

Now, when we execute the above procedure, all these operations will take place and the procedure will return a result set.

Read: SQL Server scheduled stored procedure

Create and drop temp table in stored procedure SQL Server

In the previous section, we have seen how to create different types of temp tables within a SQL Server stored procedure. Now, here, we will discuss how to create and drop a temp table in a stored procedure. Moreover, we will discuss this method for both types of temp tables.

Create and drop local temp table in stored procedure SQL Server

As discussed earlier, a local temporary table in SQL Server remains visible and accessible till the connection is active. Once the connection is closed the local temp table will be automatically deleted (dropped). And same is the case when we use it within a stored procedure.

A local temp table (starting with #) will be automatically deleted once the stored procedure is executed. Still, to have a cleaner code, it is recommended to use the DROP statement to delete the local temp table.

Let’s modify the example from the previous section and add a drop table statement to delete that temp table.

USE [sqlserverguides]
GO


ALTER PROC [dbo].[usp_localTempTbl]  
AS  
BEGIN
  --Creating local temp table
  CREATE TABLE #Country( Id INT, Country_Name VARCHAR(50) )  
  
  --Inserting data into the local temp table
  INSERT INTO #Country Values(1, 'United States')  
  INSERT INTO #Country Values(2, 'Canada')  
  INSERT INTO #Country Values(3, 'New Zealand')  
  
  --Fetching the data of local temp table
  SELECT * FROM #Country  

  --Deleting the local temp table
  DROP TABLE #Country
END 
GO

In this example, we are modifying the previously created stored procedure and adding a DROP TABLE statement. Here, the DROP TABLE statement will be used to delete the local temp table created within the procedure.

However, when we execute the above procedure, the result will remain the same as stated in the previous section.

Create and drop global temp table in stored procedure SQL Server

The global temp tables in SQL Server are a little different from local temp tables. A global temp table (starting with ##) is shared between the sessions. And these tables are deleted only when the last connection that refers to the table closes.

So, it will be best to drop it at the end of the stored procedure. So, no other connection can access it.

Let’s modify the example from the previous section and add a drop table statement to delete that temp table.

USE [sqlserverguides]
GO


ALTER PROC [dbo].[usp_globalTempTbl]  
AS  
BEGIN
  --Creating global temp table

  CREATE TABLE ##Country( Id INT, Country_Name VARCHAR(50) )  
  
  --Inserting data into the global temp table
  INSERT INTO ##Country Values(1, 'United States')  
  INSERT INTO ##Country Values(2, 'Canada')  
  INSERT INTO ##Country Values(3, 'New Zealand')  
  
  --Fetching the data of global temp table

  SELECT * FROM ##Country  

  --Deleting the global temp table

  DROP TABLE ##Country
END 
GO

Here also we have modified the previously created stored procedure and added a DROP TABLE statement.

Also, check: How to test stored procedure in SQL Server

SQL Server create temp table from select in stored procedure

In SQL Server, we generally use the SELECT INTO statement to copy data from an old table to a new one. And same is the case with the temp tables in SQL Server.

So, in this section, we will discuss how to use the SELECT INTO statement within a stored procedure to copy data into a temp table. But before that, let’s discuss the syntax of using the SELECT INTO statement in SQL Server.

SELECT columns,.. INTO #temp_table
FROM old_table

Now, let’s use the above syntax within a stored procedure to copy data and create a new temp table.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_CustomerTmpTbl]  
AS  
BEGIN
  --Creating a temp table from SELECT
  SELECT id, customer_name, country
  INTO #TmpCustomers
  FROM Customers

  --Fetching the data of global temp table
  SELECT * FROM #TmpCustomers  

  --Deleting the global temp table
  DROP TABLE #TmpCustomers
END 
GO

In the above example, first, we are creating a stored procedure name usp_CustomerTmpTbl. After this, within the procedure, we are using the SELECT INTO statement to create a new temp table named #TmpCustomers. Now, we are creating and adding data into the temp table using the Customers table.

After creation, we are using the SELECT statement to fetch data from the temp table. And in the end, we are using the DROP TABLE statement to drop the temp table.

Here is the result when we execute the above procedure in SQL Server.

Read: SQL Server stored procedure case statement

SQL Server insert into temp table from stored procedure with parameters

Till now, we have seen how to create a temp table within a stored procedure in SQL Server. Now, in this section, we will understand how to insert data from a stored procedure into a temp table.

And we will illustrate this implementation using an example in SQL Server. And for this, consider the following SQL code.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetCustByCountry]
( @country VARCHAR(50) )
AS
BEGIN
  SELECT id, customer_name, city, country 
  FROM Customers
  WHERE country = @country
END
GO

In the above example, we have created a stored procedure named usp_GetCustByCountry. And this procedure will return the customer name, id, city, and country from the Customers table. Moreover, we are also using the WHERE clause to filter records based upon a country name given as input.

CREATE TABLE #CustTmpTable
(
 id INT, 
 customer_name VARCHAR(50), 
 city VARCHAR(50), 
 country VARCHAR(50)
)

INSERT INTO #CustTmpTable
EXEC usp_GetCustByCountry 'United States'

Next, we need to create a temp table with the same columns as specified in the stored procedure. After this, we will use an INSERT statement with an EXEC statement to insert data from the procedure into the temp table. Also, while execution, we specified the input as the United States.

Now, to get the result, we can query the temp table within the same query editor page.

Read: Alter Stored Procedure in SQL Server

SQL Server insert into temp table from stored procedure without creating temp table

So, in the previous section, we have seen one method to insert into a temp table from a stored procedure. However, in that method first, we need to create a temp table and then insert data.

Now, in this section, we will discuss a method to insert into a temp table from the stored procedure without creating a temp table. Again, for this illustration, we will use the usp_GetCustByCountry procedure from the previous section.

For the implementation, we will use the OPENQUERY function to execute the procedure. Therefore, for the example, we will use the following syntax to insert data into temp without creating it.

SELECT * INTO #temp_table
FROM OPENQUERY([YOUR-SERVER-NAME], 
    'SET FMTONLY OFF; EXEC database.schema.procedureName "params"')

In the above syntax, first, we are using the SELECT INTO statement to create a temp table from a procedure. And to execute a procedure, we will use the OPENQUERY function.

Now, let’s use the syntax to implement an example in SQL Server.

USE [sqlserverguides]
GO

SELECT * INTO #CustTmpTable
FROM OPENQUERY([DESKTOP-EFDCLUP], 
    'SET FMTONLY OFF; EXEC sqlserverguides.dbo.usp_GetCustByCountry "United States"')

SELECT * FROM #CustTmpTable

Now, to get the result, we can query the temp table within the same query editor page.

Read: Rename stored procedure in SQL Server

How to use temp table in stored procedure SQL Server

In SQL Server, to use a temp table within a stored procedure, first, we need to create a temp table and then perform the required operation. This is because a temp table in SQL Server is bound to the current session. And once the session end, the temp table is automatically deleted (dropped).

Moreover, when we create and use a temp table within a stored procedure, the temp table will automatically be deleted once the execution is completed.

Now, we have already explained how to create and use the temp table within the stored procedure in some of the previous sections. For more details please refer to them.

Create index on temp table in stored procedure SQL Server

In this section, we will understand how to create an index on a temp table created within a stored procedure. Now, indexes in SQL Server are used to retrieve a table or view data quickly. An index in SQL Server is similar to an index of a book used to retrieve data quickly.

Now, for the implementation and execution of this task, we are going to use the Customers table. And we will perform the following tasks.

• First, we will create a stored procedure in SQL Server.
• After this, within the procedure, we will create a temp table using the Customers table.
• Next, we will create an index on one of the columns and arrange it in ascending order.
• In the end, we will retrieve that column with the index.
• And to get the final result, we will execute the stored procedure.

Example

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_idxTmpTable
AS
BEGIN
  --Creating a temp table from SELECT
  SELECT id, customer_name, country
  INTO #TmpCustomers
  FROM Customers
  
  --Creating a index on temp table
  CREATE INDEX test_idx ON #TmpCustomers (customer_name ASC)
  
  --Fetching the data from temp table
  SELECT customer_name, country
  FROM #TmpCustomers WITH(INDEX(test_idx))
  
  --Deleting the temp table
  DROP TABLE #TmpCustomers
END

In the above procedure, we have created a temp table named #TmpCustomers from the Customers table. After this, we have created an index named test_idx on the customer_name column. Next, we are fetching data from the temp table. In the end, we are dropping the temp table.

To get the final result, let’s execute the stored procedure.

Moreover, we can also observe the index can from the execution plan.

You may also like to read the following tutorials on SQL Server.

• SQL Server find text in stored procedure
• Could not find stored procedure in SQL Server
• How to execute stored procedure in SQL Server
• SQL Server check user permissions on table
• SQL Server Datetime functions examples

So, in this tutorial, we have learned how to use and create a temp table in stored procedures in SQL Server. Moreover, we have also illustrated the following set of topics.

• Create temp table in stored procedure in SQL Server
• Create local temp table in stored procedure in SQL Server
• Create global temp table in stored procedure in SQL Server
• Create index on temp table in stored procedure SQL Server
• Create and drop temp table in stored procedure SQL Server
• How to use temp table in stored procedure SQL Server
• SQL Server insert into temp table from stored procedure without creating temp table
• SQL Server insert into temp table from stored procedure with parameters
• SQL Server create temp table from select in stored procedure