INSERT INTO temporary table from sp_executsql

Question

Generally, I am bulding dynamic SQL statement that is executing using sp_executsql like this:

EXEC sp_executesql @TempSQLStatement 

I need to insert the return result row set in something (table variable or temporary table), but I am getting the following error:

Msg 208, Level 16, State 0, Line 1746
Invalid object name '#TempTable'.

after executing this:

INSERT INTO #TempTable
EXEC sp_executesql @TempSQLStatement 

From what I have read, I believe the issue is caused because I am not specifying the columns of the temporary table, but I am not able to do this because the return columns count varies.

I have read that I can use global temporary tables, but I have done this before and wonder is there an other way to do that.

Answer 1

You can't. There is simply no way to create a #temptable from an EXEC output schema.

INSERT ... EXEC requires the table to exists (thus must know the schema before execution).

SELECT ... INTO does not support EXEC as a source.