title: SQL Server select from temp table into another temp table
body: <p>I have this in a stored procedure:</p>
  
  <pre><code>select * into #temp_UNION from 
  (
    SELECT 6 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (588,1,6)
     UNION all
     SELECT 8 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (558,1,8)
   ) a
  </code></pre>
  
  <p>which gives:</p>
  
  <pre><code>interaction type  historyid         incentiveprogramid       points  
   6              1                  1               50
   6                  1                  4                   50
   6              1                      5                   50
   8                  1                  3                  100
   8              1                  4              100
  </code></pre>
  
  <p>then i have:</p>
  
  <pre><code>select tu.InteractionType,ipc.Name,tu.Points from #temp_UNION tu
   inner join Incentive.IncentiveProgramCultures ipc
  on tu.IncentiveProgramId = ipc.IncentivePrograms_IncentiveProgramId
   inner join Zinc.Users zu
  on zu.Cultures_DefaultCultureId = ipc.IncentiveProgramCultureId
   where zu.UserId = 588
  
  
  6   India - Q2 Incentive    50
  8   India - Q2 Incentive    100
  </code></pre>
  
  <p>now i need to make up HintText which is a field in my #CategoriesTable(previously defined) by using the name i got from above and the points</p>
  
  <pre><code>UPDATE #CategoriesTable
  SET HintText = CASE WHEN HasAssessment = 1 
                      THEN 'Program ' + tu.name + ' will earn you ' + tu.points
                      ELSE 'With No Assessment' 
                  END 
  </code></pre>
  
  <p>but i get an error on tu.Name: multi part identifier could not be bound?
  how can i achieve? should i make use of another temp table with the 2 rows in it?</p>
  
  <p>here new code:</p>
  
  <pre><code> select * into #temp_UNION from 
  (
   SELECT 6 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (588,1,6)
   UNION all
   SELECT 8 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (558,1,8)
   ) a
  
   select tu.InteractionType,ipc.Name,tu.Points,zu.UserId  into #temp1 from #temp_UNION tu
   inner join Incentive.IncentiveProgramCultures ipc
   on tu.IncentiveProgramId = ipc.IncentivePrograms_IncentiveProgramId
   inner join Zinc.Users zu
   on zu.Cultures_DefaultCultureId = ipc.IncentiveProgramCultureId
   where zu.UserId = 588
  
  Select * from #temp1 t
  UPDATE #CategoriesTable
    SET HintText = CASE WHEN HasAssessment = 1 
                      THEN 'Program ' + t.Name + ' and points = ' + t.Points
                      ELSE 'With No Assessment' 
                  END 
  FROM #temp1 t
  WHERE t.userId = #CategoriesTable.UserId
  
  DROP TABLE #temp_UNION 
  DROP TABLE #temp1
  
  SELECT *
  FROM #CategoriesTable
  </code></pre>
  
  <p>im not getting the #CategoriesTable?</p>