Global Temp Table

• texpic

  SSCertifiable

  Points: 5885

  More actions

  March 5, 2017 at 12:48 pm

  #321060

  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: 5885

  More actions

  March 5, 2017 at 12:57 pm

  #1931714

  I figured it out, if I move the

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