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

Otávio Décio · Accepted Answer · 2009-05-08 17:46:41Z

8

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.

answered May 8, 2009 at 17:46

Otávio Décio

74.6k18 gold badges167 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tomalak Over a year ago

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

HLGEM Over a year ago

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.

j0tt · Accepted Answer · 2009-05-08 17:50:17Z

5

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 #.

answered May 8, 2009 at 17:50

j0tt

1,1081 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related