Exec Stored Procedure into Temp Table

In SQL Server, executing a stored procedure into a temporary table is a common task when you need to capture the output of a stored procedure for further manipulation or analysis. 

This can be useful in various scenarios, such as reporting, data transformation, or simply filtering the data returned by the stored procedure. This article will walk you through different methods to achieve this, with detailed explanations for each.

How to Create a Temp Table from a Stored Procedure?

Below are three effective methods to seamlessly capture stored procedure results in a temporary table.

Method 1: Using INSERT INTO … EXEC

One of the most straightforward ways to execute a stored procedure and store its results into a temporary table is by using the INSERT INTO … EXEC statement. This method involves creating a temporary table with a structure that matches the output of the stored procedure and then inserting the results directly into this table.

Here’s the Step-by-Step Process:

1. Create the Temporary Table
First, you need to define a temporary table with columns that match the structure of the stored procedure’s result set. If the stored procedure returns a table with specific columns and data types, your temporary table must match those exactly.

CREATE TABLE #TempTable (
    Column1 INT,
    Column2 NVARCHAR(100),
    Column3 DATETIME
);

In this example, #TempTable is a temporary table with three columns. This structure must match the output of the stored procedure you intend to execute.

2. Execute the Stored Procedure and Insert the Results

Once the temporary table is ready, you can use the INSERT INTO … EXEC statement to execute the stored procedure and insert the results into the temporary table.

INSERT INTO #TempTable

EXEC dbo.MyStoredProcedure @Parameter1 = 'Value1', @Parameter2 = 10;

Here, the EXEC dbo.MyStoredProcedure command runs the stored procedure MyStoredProcedure with the provided parameters. The result is then inserted into #TempTable.

3. Using the Data

After the data is inserted into the temporary table, you can manipulate it just like any other table.

SELECT * FROM #TempTable WHERE Column1 > 100;

This query selects all rows from #TempTable where Column1 is greater than 100.

4. Cleaning Up

Don’t forget to drop the temporary table after you’re done to free up resources.

DROP TABLE #TempTable;

Method 2: Using SELECT INTO

Another approach is to use the SELECT INTO statement in conjunction with EXEC to create the temporary table and populate it in one step. This method is useful when you don’t know the structure of the result set beforehand or want to avoid defining the temporary table manually.

1. Execute the Stored Procedure with SELECT INTO
With this method, you can execute the stored procedure and create the temporary table on the fly.

SELECT *

INTO #TempTable

FROM OPENROWSET('SQLNCLI11', 'Server=.;Trusted_Connection=yes;', 

    'EXEC dbo.MyStoredProcedure @Parameter1 = ''Value1'', @Parameter2 = 10');

In this case, OPENROWSET is used to execute the stored procedure and capture its output. The SELECT INTO statement then creates #TempTable and populates it with the result set.

2. Using the Data

As before, you can work with #TempTable just like any other table.

SELECT * FROM #TempTable WHERE Column2 LIKE 'A%';

This query retrieves all rows where Column2 starts with the letter ‘A’.

3. Cleaning Up

Again, ensure that you drop the temporary table after use.

DROP TABLE #TempTable;

Method 3: Using a Table Variable

In some cases, you might prefer to use a table variable instead of a temporary table. Table variables are stored in memory and can offer performance benefits in certain scenarios. However, they have limitations, such as a lack of support for indexes and constraints.

1. Declare the Table Variable
First, declare a table variable with a structure matching the stored procedure’s output.

DECLARE @TempTable TABLE (

    Column1 INT,

    Column2 NVARCHAR(100),

    Column3 DATETIME

);

2. Execute the Stored Procedure and Insert the Results

Use INSERT INTO … EXEC to execute the stored procedure and store its results in the table variable.

INSERT INTO @TempTable

EXEC dbo.MyStoredProcedure @Parameter1 = 'Value1', @Parameter2 = 10;

3. Using the Data

You can select and manipulate the data from the table variable just like with a temporary table.

SELECT * FROM @TempTable WHERE Column3 BETWEEN '2024-01-01' AND '2024-12-31';

This query selects all rows where Column3 falls within the year 2024.

4. No Need for Cleanup

Table variables automatically go out of scope when the batch, stored procedure, or function in which they are declared ends, so there’s no need to manually drop them.

Frequently Asked Questions 

Can we create table using stored procedure in SQL?

The stored procedure constructs a dynamic SQL statement using the CONCAT function to combine provided parameters. This statement is then executed to create the desired table. 

Can I use a temporary table if the stored procedure returns multiple result sets?

Yes, but it requires additional handling. You can capture each result set in different temporary tables by executing the stored procedure multiple times or using more advanced techniques such as using the sp_describe_first_result_set system stored procedure to identify the structure of each result set.

Conclusion

Executing a stored procedure into a temporary table or table variable is a powerful technique in SQL Server that allows for further manipulation of the data returned by the procedure. The methods outlined—INSERT INTO … EXEC, SELECT INTO, and using a table variable—each have their own strengths and are suitable for different scenarios. So, decide accordingly whichever allows you the most convenience.