title: Select * into Temp Table from Dynamic SQL Results
body: <p>I wrote a dynamic pivot and need to input the results into a temp table to join the results to another table. </p>
  
  <p>Perhaps there is a better way to get the results I want without inserting into a Temp table? Could I join a table inside the Dynamic Pivot to get the final result I want?</p>
  
  <p>Getting the errors:</p>
  
  <pre class="lang-none prettyprint-override"><code>Msg 263, Level 16, State 1, Line 59
  Must specify table to select from.
  
  Msg 1038, Level 15, State 5, Line 59
  An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
  </code></pre>
  
  <p>My code is the following:</p>
  
  <pre><code> declare @RealYear as nvarchar(max)
  select @RealYear= '2016'
  IF OBJECT_ID('tempdb.dbo.#TempCosts', 'U') IS NOT NULL
    DROP TABLE #TempCosts; 
  
  IF OBJECT_ID('tempdb.dbo.#TempCostsFinal', 'U') IS NOT NULL
    DROP TABLE #TempCostsFinal; 
  
  select * into #TempCosts from
  (
  --Journal Entries
  select * from lth.dbo.vwJournalEntries_LTWS1
  union all
  --Non Journal Entries
  select *,'' from lth.dbo.vwweeklydebits_ltws1
  )D
  
  --select * from #TempCosts
  DECLARE @cols AS NVARCHAR(MAX)
  declare  @query  AS NVARCHAR(MAX)
  declare @temptable as nvarchar(max)
  
  select @cols = STUFF((SELECT ',',  QUOTENAME(AccountRef_FullName) 
    from #TempCosts
                      group by AccountRef_FullName
                      having accountref_Fullname is not null
                      order by AccountRef_FullName
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  set @query = 'SELECT *,  ' + @cols + ' from 
               (
                  select *
                  from #TempCosts
                  where year = ' + @RealYear + '
              ) x
              pivot 
              (
                  sum(Amount)
                  for AccountRef_FullName in (' + @cols + ')
              ) p 
  
          '
  
  
          execute(@query)
  
  
      select * into #TempCostsFinal Execute(@query)
  </code></pre>