• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

SQL 2008 create dynamically named temp table

DECLARE @tab nvarchar(255), @sql varchar(255)
SET @tab = '#mytable'
SET @sql='create table ' + @tab + ' (id int, name varchar(255))'
select @sql

INSERT INTO @tab values (100, 'a'), (200, 'b'), (300, 'c')


When trying to run this in Management Studio, I get....

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@tab'.

Any ideas?
  • 5
  • 4
  • 3
2 Solutions
Simone BSenior E-Commerce AnalystCommented:
You're inserting into @tab directly, but you have declared it as a string variable. If you want to insert into it, it should be a table variable. Otherwise, you could just insert into your temp table then select from that.
Simone BSenior E-Commerce AnalystCommented:
You could just use this code below to get the same result. Is there a specific reason you want to use a table variable and dynamic sql?

CREATE TABLE #mytable (id int, name varchar(255))

INSERT INTO #mytable values (100, 'a'), (200, 'b'), (300, 'c')

SELECT * FROM #mytable

DROP TABLE #mytable

Open in new window

Simone BSenior E-Commerce AnalystCommented:
In order to make this work using a dynamically named temp table, then your last 3 SQL statements also have to be dynamic sql:

DECLARE @tab nvarchar(255)
, @sql1 varchar(255)
, @sql2 varchar(255)
, @sql3 varchar(255)
, @sql4 varchar(255)

SET @tab = '#mytable'
SET @sql1 ='create table ' + @tab + ' (id int, name varchar(255))'
select @sql1 
EXEC @sql1

SET @sql2 = 'INSERT INTO ' + @tab + 'values (100, ''a''), (200, ''b''), (300, ''c'')'
EXEC @sql2

SET @sql3 = 'SELECT * FROM ' + @tab
EXEC @sql3

SET @sql4 = 'DROP TABLE ' + @tab
EXEC @sql4

Open in new window

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Aneesh RetnakaranDatabase AdministratorCommented:
the scope of the temp table created using the EXEC statement wont be available to the other statements; So your best option is Not to go with a dynamic temp table as shown by Buttercup in his second post.

drl1Author Commented:
That's part of the problem as I wish to dynamically name the table in the first place so can't explicitly name and call it, hence the use of a variable. I believe this can be done. The following code (pulled from the web) works, for example...

declare @temp_table_name sysname
set @temp_table_name = '#'+replace(newid(),'-','')

exec ('create table '+@temp_table_name+'
USER_ID UniqueIdentifier default NewId(),
USER_NAME nvarchar(50)

insert into '+@temp_table_name+' (USER_NAME) VALUES (1)
select * from '+@temp_table_name+'
However, having run the above (which returns one row) the following select statement does not work...

select * from @temp_table_name

and neither does this...

EXEC('select * from '+@temp_table_name+'')

I'm looking to dynamically name the table, then insert items in to it, then select items from it for processing, then drop it.
drl1Author Commented:
Thanks buttercup. I already have the hard coded temp table and that's fine, but I am playing around with dynamic statements and this was one thing that I wondered if I could achieve. My other requirements involve dropping values dynamically in to variables in select statements and then executing them, so i'm just scoping out what I can and can't do in this way.

I take it any dynamic code has to be composed as a string first before then being encapsulated in an EXEC command.
Aneesh RetnakaranDatabase AdministratorCommented:
>I wish to dynamically name the table in the first place so can't explicitly name and call it, hence the use of a variable. I believe this can be done

Any particular reason for this ? As i mentioned earlier, if you create a temp table #temp from one user session, it wont be accassable from another user session.So i would suggest you should go with an explicit name;  if you want a dynamic name, you need to include all your statements  within a dynamic sql statement; (your create table , insert table and drop table should be in one sql statement).
Simone BSenior E-Commerce AnalystCommented:
Yes, that's correct.

Also Aneesh is correct, the scope wouldn't work as shown. But you could create a procedure and have your @tab as a parameter, in which case you could use that variable throughout the procedure.
drl1Author Commented:
I realise that the local temp table can't be accessed by other sessions. As I mentioned, I'm looking at dynamic statements in general, and this one cropped up making me wonder if it was possible. That aside, I was earlier executing a stored procedure (includes a loop which iterates a few thousand times and takes a minute or so to complete) which created a local temp table but the procedure was manually stopped part way through. As the procedure didn't complete it meant that the temp table wasn't dropped and so running the procedure again threw an error. This got me thinking about whether the table creation could be dynamic. I guess that would still leave an orphaned temp table somewhere until flushed from memory.

Other dynamic things I'm looking at (unrelated to the above) would result in the dynamic compilation of select statements, for example....

select count(@whatever) from @mytable where @value = @something


select top @count @field1, @field2, @field3 from @mytable where @value between @x and @y

...all variables would be dynamically passed in to the procedure for processing. Anyhow, this is beyond the scope of the original question, which I think I'll close and award points. Thanks for your input.
Simone BSenior E-Commerce AnalystCommented:
Hmmm... even in a stored proc it won't recognize the variable. The @sql variables are created properly, but they can't be executed. I'll stick to my original response, where a straight temp table is used.
Aneesh RetnakaranDatabase AdministratorCommented:
if you are looking to return a value from a dynamic sql check my post here

drl1Author Commented:
That's certainly along the lines of what my final implementation might look like, but with 10 or 20 dynamically constructed statements joined together with union all to create a record set. Alternatively, and probably more efficiently, each dynamic statement will individually write its outputs to a temp table from which the final processing will be done.

Thanks all for your input.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now