GilaMonster (12/17/2008)
If you create the temp table in dynamic SQL, the temp table will go out of scope and be dropped as soon as the dynamic SQL ends. There's no way around that, not even using global temp tables
This fails as expected with "Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#Temp'."
DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT TOP 10 * INTO #Temp FROM master.dbo.syscolumns'
EXEC(@SQL)
SELECT * FROM #Temp
This works...
DECLARE @SQL VARCHAR(200)
SET @SQL = 'SELECT TOP 10 * INTO ##Temp FROM master.dbo.syscolumns'
EXEC(@SQL)
SELECT * FROM ##Temp
.. the global temp table is available outside the scope of the dynamic sql.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden