Insert into temporary table from Stored Procedure

Posted on May 6, 2008

14


For this to work, you need SQL Server 2008.

We use temporary tables and table variables occasionally and sometimes need to fill those temporary objects with data from the results of a stored procedure or dynamically built query (executed with sp_ExecuteSQL).  This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.

DECLARE @jobs TABLE
( jobcount int
)
INSERT
INTO @jobs
EXEC
sp_executesql
N’select 1 AS jobcount’
SELECT
*
FROM @jobs

Here, I define a table variable and then insert into this from the execution of a query. I then select from the variable to proove that data was populated. This could easily be a Stored Procedure.

I am sure you can find uses for this , I think this is another good example of the lesser known additions Microsoft has included in 2008.

Update: I’ve recently needed to insert the results of a stored procedure into a new table so I could confirm the exact schema returned by the stored procedure. Find out how this can be done in the following post:

http://www.barry-king.com/2012/10/18/inserting-the-results-of-a-stored-procedure-into-a-table/

Advertisements
Posted in: SQL, sql 2008