"Invalid object name" when using global temp table in stored procedure

Question

I'm following several examples to use global temp table such as this & this

I have following query to get data into a global temp table:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'SELECT * INTO ##tmpDraftTableData  
             FROM ' + @DraftTableName 

EXEC sp_executesql @SQL

Note that the @DraftTableName is dynamic and come from different table with different structure, that's why I need to use dynamic query.

And then I want to insert the data as obtain from above global temp table into another temp table to loop and process the data:

SELECT *
INTO #tmpDraftTableData
FROM ##tmpDraftTableData  -- ERROR: Invalid object name ##tmpDraftTableData

Seems like I can't use the global temp table with the error

Invalid object name ##tmpDraftTableData

unlike other sample code.

What did I do wrong here?

"...into another temp table to loop and process the data". If you know which columns or structure your looping data should have, would you mind considering creating a well-known temporary table and inserting the columns you need from @DraftTableName? If the temporary table is created outside the dynamic SQL, you will be able to select it's contents outside of the dynamic SQL also. — EzLo, Mar 27 '18 at 08:20

score 0 · Answer 1 · answered Mar 27 '18 at 08:07

Try this:

CREATE TABLE ##tmpDraftTableData (ID INT)
INSERT INTO ##tmpDraftTableData VALUES(1)
INSERT INTO ##tmpDraftTableData VALUES(2)
INSERT INTO ##tmpDraftTableData VALUES(3)

DECLARE @SQL NVARCHAR(MAX), @DraftTableName VARCHAR(100)='##tmpDraftTableData'

SET @SQL = N'SELECT * 
            INTO #tmpDraftTableData  
             FROM ' + @DraftTableName +'

             SELECT * FROM #tmpDraftTableData
             '

You can directly execute the query, instead of using sp_executesql.

PRINT (@SQL)
EXEC (@SQL)

Output:

ID
1
2
3

"Invalid object name" when using global temp table in stored procedure

1 Answers1