Global Temp Table

  • texpic

    SSCertifiable

    Points: 5882

    If I use #Final as a table (instead of #3Final) I get a message Invalid Object Name #Final.  Is there a way not to use a Global Temp table (assuming I want to use a temp table?)  Thanks.

    IF OBJECT_ID('tempdb..#Distinct') > 0 DROP TABLE #Distinct

    CREATE TABLE #Distinct

    (Animal varchar(99), Color varchar(99))

    INSERT INTO #Distinct

    SELECT 'Dog', 'White' UNION ALL

    SELECT 'Dog', 'Blue' UNION ALL

    SELECT 'Dog', 'Black' UNION ALL

    SELECT 'Dog', 'Purple' UNION ALL

    SELECT 'Dog', 'Orange' UNION ALL

    SELECT 'Horse', 'Brown' UNION ALL

    SELECT 'Cat', 'White' UNION ALL

    SELECT 'Cat', 'Blue' UNION ALL

    SELECT 'Cat', 'Purple'

     

    IF OBJECT_ID('tempdb..##Final') > 0 DROP TABLE ##Final

    DECLARE @SQLStatement nVARCHAR(MAX) = 'SELECT Animal'

    SELECT @SQLStatement = @SQLStatement + ', ' + CHAR(13) + '['+ Color + '] = MAX(CASE WHEN Color = ''' + Color + ''' THEN ''Y'' ELSE ''N'' END) '

    FROM #Distinct A

    GROUP BY Color ORDER BY Color

    SELECT @SQLStatement = @SQLStatement + CHAR(13) + 'INTO ##Final FROM #Distinct GROUP BY Animal'

    EXEC (@SQLStatement)

    --PRINT (@SQLStatement)   

    SELECT * FROM ##Final

  • texpic

    SSCertifiable

    Points: 5882

    I figured it out, if I move the

    SELECT * FROM #Final
    into the @SQLstatement it will work.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply