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.
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;