title: Dynamically create temp table, insert into temp table, and then select
body: <p>Basically i want to be able to dynamically create a temp table based off of an existing table, and then insert values into the temp table, and select the inserted values.</p>
  
  <p>i've got the part where i can create the temp table working just fine, it's just that inserting and selecting form it aren't working too well.</p>
  
  <p>here's my current code.</p>
  
  <pre><code>declare @table table 
  (
      OrdinalPosition int, 
      ColumnName nvarchar(255), 
      DataType nvarchar(50), 
      MaxChar int, 
      Nullable nvarchar(5)
  )
  declare @i int
  declare @count int
  declare @colname nvarchar(255), @datatype nvarchar(50), @maxchar int
  declare @string nvarchar(max)
  declare @tblname nvarchar(100)
  
  set @tblname='Projects'
  
  set @string='create table #' + @tblname + ' ('
  
  insert into @table 
  (
      OrdinalPosition, 
      ColumnName, 
      DataType, 
      MaxChar, 
      Nullable
  )
  
  SELECT    
      ORDINAL_POSITION ,
      COLUMN_NAME ,
      DATA_TYPE ,
      CHARACTER_MAXIMUM_LENGTH ,
      IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = @tblname
  
  set @i=1
  select @count=count(*) from @table
  
  while (@i&lt;=@count)
  begin
  
      select @colname=ColumnName from @table where OrdinalPosition=@i
      select @datatype=DataType from @table where OrdinalPosition=@i
      select @maxchar=MaxChar from @table where OrdinalPosition=@i
  
      if (@maxchar is null)
      begin
  
          set @string = @string + @colname + ' ' + @datatype
  
      end
      else
      begin
  
          set @string = @string + @colname + ' ' + @datatype + '(' + cast(@maxchar as nvarchar(20)) + ')'
  
      end
  
      if (@i=@count)
      begin
  
          set @string = @string + ')'
  
      end
      else
      begin
  
          set @string = @string + ', '
  
      end
  
  
      set @i=@i+1
  end
  
  select @string
  
  exec(@string)
  
  set @string='
  insert into #Projects (pk_prID, prWASSN_ID, prProjectStatus, prBusinessUnit, prServiceLine, prStudyTypeCode, prStudyNumber, prTimePoint, prStudyDirector, 
                        prGroupLeader, prBookedDate, prBookedAmount, prConsumed, prBudgetedHours, prFinalReport, prFinalYear, prFinalMonth, prStartQA, 
                        prLabWorkStarted, prLabWorkCompleted, prProjImpDate, prCompanyName, prCompanyNumber, prIsFTE, prRevisedDeadlineDate, prProjectFinalized, 
                        prBookedYear, prBookedMonth, prCRMQuoteID, prLineItemNumber, prDraftReport, prInternalTargetDeadlineDate, prProtocolSignedDate, 
                        prDataToRWS, prRWSWorkStarted, prFirstDraftToPL, prFirstDraftToQA, prArchivedDate, prToPLForQACommentReview, 
                        prAnticipatedProjectArchiveDate, prToQAWithPLCommentResponse, prProjectReactivatedDate, prQAFinishDate, prSecondDraftReportToClient)
  select *
  from cube.Projects'
  
  select @string
  
  exec (@string)
  
  set @string='select * from #Projects'
  
  exec (@string)
  </code></pre>
  
  <p>this is the error that i get:</p>
  
  <p>(44 row(s) affected)</p>
  
  <p>(1 row(s) affected)</p>
  
  <p>(1 row(s) affected)
  Msg 208, Level 16, State 0, Line 2
  Invalid object name '#Projects'.
  Msg 208, Level 16, State 0, Line 1
  Invalid object name '#Projects'.</p>