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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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