4

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.

gotqn
  • 42,737
  • 46
  • 157
  • 243

2 Answers2

5

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    So, I should use a global temporary table in the dynamic SQL statement to store the data and then to extract the data from it? – gotqn Nov 01 '13 at 09:54
  • 1
    You should ask about a solution, but instead post the actual problem you're trying to solve. There are ways to achieve what you want, but is much better if you explain *why*. In general, try to avoid having to deal with unknown/untderemined result shapes, you'll keep hitting this problem and 'solutions' would simply shift the problem somewhere else, never solve it. SQL is a *not* a dynamic language. – Remus Rusanu Nov 01 '13 at 09:57
  • 1
    Actually, knowing that I am not able to do this is quite enough. I will just change my logic to handle the situation. Thanks for helping me to clear this out. – gotqn Nov 01 '13 at 09:59
3

If you use INSERT INTO statement you have to create a table first.

Another way if you want to store SQL statement result into the temp table you can use SELECT ... INTO but in this case you should change @TempSQLStatement and add INTO #TempTable before FROM to get it.

For example if your @TempSQLStatement contains only one FROM keyword:

SET @TempSQLStatement=REPLACE(@TempSQLStatement,' FROM ',' INTO ##TempTable FROM ');
EXEC sp_executesql @TempSQLStatement; 

SELECT * from ##TempTable;
valex
  • 23,966
  • 7
  • 43
  • 60