CODEWITHSUNDEEP
sql-server-2008
sk7730
sk7730

Reputation: 736

SQL Dynamic Temp Table

I have a table dbo.CTC_GRP...Below is table name and values...

**CTC_GRP_DS**
Bank Contact
Dept2
CS

I am trying to create a temp table as below..These are column names generated from table column values

   Proj_ID   Bank Contact     Dept2     CS

My attempts are below...

  DECLARE @Dept NVARCHAR(MAX)
  DECLARE @DeptQry NVARCHAR(MAX)


  SELECT  @Dept = STUFF((SELECT ',' +  QUOTENAME(CTC_GRP_DS) + ' varchar(max)'
                from dbo.CTC_GRP
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

 set @DeptQry = N'Create Table #temptable ( Proj_ID int,' + @Dept + ' '+') '

 print @DeptQry

 EXEC sp_executesql @DeptQry;

Till printing @DeptQry i am able to arrive at, how can i excute those dynamic created sql... can anyone help me on it...

Upvotes: 0

Views: 2503

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

A temp table created within an EXEC/sp_executesql will be destroyed when that call exits. You need to create the table in the outer scope. You can then modify it with EXEC calls. SOmething like:

create table #temptable (Proj_ID int)

EXEC sp_executesql N'alter table #temptable add [Bank Contact] nvarchar(max)'
EXEC sp_executesql N'alter table #temptable add [Dept2] nvarchar(max) int'

Each new column needs to be added via a separate alter table statement, unfortunately.

And obviously, everything that manipulates this table will also have to be dynamic SQL.

Upvotes: 5

Related Questions