Create Temporary table with variable as column nam

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create Temporary table with variable as column nam

Author

Topic

uttam548
Starting Member

12 Posts

Posted - 2009-03-04 : 05:36:03

Frens,
I need to create a temporary table with variable values as column
name as shown in the following snippet. Is there any way to accomplish this?
declare @field1 nvarchar(20)
set @field1 = 'Field1'
create table #testtable
(@field1 nvarchar(20))
select * from #testtable
Your help will be highly appreciated.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-04 : 05:50:44

Try


declare @field1 nvarchar(20)
set @field1 = 'Field1'
exec('create table testtable 
('+ @field1 +' nvarchar(20))')

select * from testtable

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

uttam548
Starting Member

12 Posts

Posted - 2009-03-04 : 06:18:26

Thanks Mangal for your reply!
But i need to create a temporary table and select values from it.
Any suggestions??

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 06:25:09

You can select values after you create the table dynamically as shown above. What exactly is your problem ?

uttam548
Starting Member

12 Posts

Posted - 2009-03-04 : 06:34:20

yeah i can select columns from the table created by above query. But it creates table which is not desirable in my case, because my system involves multiple users.
My problem is when i create temporary table this way, it is not recognized in the select statement.
exec('create table #testtable ('+ @field1 +' nvarchar(20))')
select * from #testtable
Advise me.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-04 : 06:43:17

declare @field1 nvarchar(20)
set @field1 = 'Field1'
exec('create table #testtable
('+ @field1 +' nvarchar(20))
select * from #testtable
')

Karthik

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-04 : 06:52:35

quote:
Originally posted by uttam548

yeah i can select columns from the table created by above query. But it creates table which is not desirable in my case, because my system involves multiple users.
My problem is when i create temporary table this way, it is not recognized in the select statement.
exec('create table #testtable ('+ @field1 +' nvarchar(20))')
select * from #testtable
Advise me.

Change it to a global temporary table then.

exec('create table ##testtable ('+ @field1 +' nvarchar(20))')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-04 : 07:01:49

quote:
Originally posted by uttam548

Frens,
I need to create a temporary table with variable values as column
name as shown in the following snippet. Is there any way to accomplish this?
declare @field1 nvarchar(20)
set @field1 = 'Field1'
create table #testtable
(@field1 nvarchar(20))
select * from #testtable
Your help will be highly appreciated.

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

uttam548
Starting Member

12 Posts

Posted - 2009-03-06 : 00:35:33

@sakets_2000 -> Thanks for your suggestion but global table is not permitted in my case.
I need to show entities wise permission for different user groups.
so i am trying to dynamically list permission name as columns and show each entities in row
with their permission in checkbox field.
Can you suggest me the better approach?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources