Dynamically create temp table, insert into temp table, and then select

Question

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.

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.

here's my current 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<=@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)

this is the error that i get:

(44 row(s) affected)

(1 row(s) affected)

(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'.

Solution 1

Try to name the table with two ##, this will create a global temp table. It could be an issue with scoping, you might be creating the table with exec but it is not visible when it comes back.

Solution 2

When you call exec I believe it executes outside the context where your temp table was declared I believe if you appended your strings together and executed as one call to exec it would succeed. The other option is to use a global temp table with ## as the prefix instead of #.

Comments

[Tomalak]

+1 I was just in the process of typing up the exact same thing. :)

[dburges]

Do remeber that ## is global, other people/processes besides your process will be able to access it. This can create real problems if your proc runs concurrently or someone else drops and recreates a global temp table wit the same name.