Insert stored procedure output into a temporary table

Question

when I try to write the output of a stored procedure to a temporary table, I get an error message

Msg 208, Level 16, State 0, Line 4
Invalid object name '#tblTemp'.

My request is as follows:

DECLARE @group_name varchar(250) SET @group_name = 'somevalue' INSERT INTO #tblTemp EXEC mySchema.sp_MyStoredProc @group_name OUTPUT SELECT * FROM #tblTemp DROP TABLE #tblTemp 

What is wrong here?

Thank you for your help!

Answer 1

To use a temporary table this way in INSERT INTO , you must first define this table.

 CREATE TABLE #tblTemp( ID int, .... ) 

In T-SQL, a temporary table can be created automatically using the following command:

 select * into #tblTemp from table 

You can use this syntax for your stored procedure results using OPENROWSET .

Here is an answer to SO that might help

Answer 2

You need to create a #temp table, for example. a CREATE TABLE before you can INSERT INTO it.