How to insert Stored Procedure result into a temporary table in SQL Server

To show how to copy stored procedure result into temporary table in SQL Server we will use the Production.Location table in AdventureWorks database.

First we need to create a stored procedure called test_procedure:

USE AdventureWorks
CREATE PROCEDURE dbo.test_procedure
AS
SELECT LocationID, Name FROM [AdventureWorks].[Production].[Location]

Here are the results of executing the stored procedure:

EXEC dbo.test_procedure

Then we need to create a temporary table in which these results will be inserted:

CREATE TABLE #temp_table([ID] int, Name varchar(100))

To insert stored procedure result into temporary table:

INSERT INTO #temp_table
exec test_procedure

Let's see what is in the temporary table:

SELECT *
FROM #temp_table

To drop the temporary table:

DROP TABLE #temp_table

If the post helped you, please share it:
Pin It

4 comments to How to insert Stored Procedure result into a temporary table in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">