How to Insert Stored Procedure Results into a Temporary Table in SQL Server

In this tutorial, I will teach you how to insert stored procedure results into a temporary table using different methods.

But first, let me do some brief explanations and highlight why knowing how to insert results from stored procedures is important.

Stored Procedures

A stored procedure is a precompiled collection of one or more SQL statements that are stored in the database server and can be executed anytime.

Stored procedures are used to encapsulate and simplify the use of lengthy or complex code. It helps database developers to ensure modularity, security, optimal performance, and consistency.

CREATE PROCEDURE procedure_name
	@parameter_name 
AS
BEGIN	
	SELECT * FROM table_name WHERE column_name = @parameter_name
END;

Temporary Tables

In SQL Server, temporary tables are used to store intermediate data. They differ from permanent tables because they are saved in tempdb database and deleted when the author’s sessions end.

They are often used to simplify complex queries, store intermediate datasets, and improve query performance.

Variable Tables

A table variable stores a temporary table in the tempdb database just like temporary tables. It works just like temporary tables but has more limitations such as no support for constraints like identity columns, truncate statements, alter statements, indexes, and so on.

DECLARE @temp_table TABLE (id INT, text_column VARCHAR(255));

OPENROWSET

OPENROWSET is a function in SQL Server that can access remote data from an OLE DB data source, Azure storage account, or file.

This function can read different file formats and is commonly used to bulk import data from files into tables or views. However, the common drawback is permission issues. If you work in medium to large organizations, you may not be able to use OPENROWSET effectively.

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=server_name;Trusted_Connection=yes;', 'SELECT * FROM database_name.dbo.table_name');

In this tutorial, I will be using the AdventureWorks database as my dataset. You download a copy from GitHub.

How to insert stored procedure results into temporary tables

Synthax:

CREATE TABLE #temp_table (
column1 datatype1,
column2 datatype2,
…
);

INSERT INTO #temp_table
EXEC stored_procedure_name;

Now, let’s practice using the AdventureWorks database.

CREATE TABLE #temp_table(
ProductAssemblyID INT
,ComponentID INT
,ComponentDesc NVARCHAR(255)
,TotalQuantity DECIMAL(18,2)
,StandardCost DECIMAL(18,2)
,ListPrice DECIMAL(18,2)
,BOMLevel INT
,RecursionLevel INT
)

INSERT INTO #temp_table
EXEC [uspGetBillOfMaterials] 754, '2010-06-19'

This code creates a temp table, executes the uspGetBillOfMaterials stored procedure, and inserts the results into the temp_table table.

How to insert stored procedure results into a table variable

Alternatively, let’s use a table_variable instead of a temp_table.

Syntax to create table variable:

DECLARE @table_variable TABLE (
column1 datatype1,
column2 datatype2,
…
);

INSERT INTO @table_variable
EXEC stored_procedure_name;

Now, let’s practice inserting results from the stored procedure into a table variable using the AdventureWorks database.

DECLARE @temp_table TABLE (
ProductAssemblyID INT
,ComponentID INT
,ComponentDesc NVARCHAR(255)
,TotalQuantity DECIMAL(18,2)
,StandardCost DECIMAL(18,2)
,ListPrice DECIMAL(18,2)
,BOMLevel INT
,RecursionLevel INT
)

INSERT INTO @temp_table
EXEC [uspGetBillOfMaterials] 754, '2010-06-19'

This code declares a table variable @temp_table, executes the uspGetBillOfMaterials stored procedure, and then inserts the result into the table variable.

How to insert stored procedure results without explicitly declaring columns

Syntax to insert stored procedure results without explicitly declaring columns.

SELECT column1, column2, …
INTO new_table
FROM OPENROWSET('SQLNCLI', 'Server=server_name;Trusted_Connection=yes;',
'EXEC stored_procedure_name');

Using the AdventureWorks database, let’s practice inserting results from the stored procedure into a temp table without explicitly declaring columns.

SELECT *
INTO #temp_table
FROM OPENROWSET('SQLNCLI'
,'Server=localhost;Trusted_Connection=yes',
'EXEC [AdventureWorks2014].[dbo].[uspGetBillOfMaterials] @StartProductID = 754, @CheckDate = ''2010-06-19''
');

If you are having issues with the code above, check out this article on common issues using OPENROWSET and how to resolve them.

Found this article helpful, check out the SQL category for more content.