Creating a Temp Table with Column Names based on Rows in another table

[TerrieS]

My goal is to have a temporary table that almost looks like an Excel pivot table with User Access Groups as the Columns across ( with the first column being the Screen Names)

i.e.

Form AP_Entry AP_Supervisor AP_Reporting
Vendor Maintenance X
Voucher Entry X
Voucher Posting X
Voucher Reporting X X X

I don't want to hard-code the table column names as we add/remove user groups fairly frequently. My first goal is to create the temp table. Not happening. If I execute the results of the print @cmd below - it creates the table no problem.

*I'm using a function that roles up the columns into a comma-separated field, but for simplicity sake I've commented that out below. So Here is a shortened version of a temp table with two columns with an insert of data into the first column only...I think anyone can use the basic script below substituting any table name...

DECLARE @Cmd VARCHAR(1000)

SELECT TOP 1 @Cmd = 'Create table #TGS_Temp (Form_Desc varchar(50),' +

--+ dbo.phm_UserGroupString_fn (group_id) +' varchar(50)

'testColumn2 varchar(50))'

FROM smmenus s

--CROSS APPLY smgrphdr g



PRINT @cmd

EXEC (@cmd)



INSERT #TGS_Temp (Form_Desc)


select 'Test insert data'

FROM smmenus

I keep getting an error "Invalid object name #TGS_Temp"

I would really like to know why the above doesn't work. However, since my overall goal is to create the column names based on rows in another table, I'm open to another suggestion on how best to accomplish this.

Thanks in advance for your time in looking at this.

 

[markros]

If you create a temp table in the separate batch executed by exec (@SQL) command, then this temp table will be only visible to that batch. In other words, you can not create it dynamically and then expect to get records from it or manipulate with it outside of that dynamic query.

PluralSight Learning Library

 

[TerrieS]

Thank you : ) That worked when I removed the # (or changed it to ##). I don't do the temp table building often enough...