[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL 2008 create dynamically named temp table

Posted on 2013-01-10
Medium Priority
Last Modified: 2013-01-10
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?
Question by:drl1
  • 5
  • 4
  • 3
LVL 11

Expert Comment

by:Simone B
ID: 38764761
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.
LVL 11

Expert Comment

by:Simone B
ID: 38764772
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

LVL 11

Accepted Solution

Simone B earned 1200 total points
ID: 38764806
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38764830
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.


Author Comment

ID: 38764831
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.

Author Comment

ID: 38764868
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.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38764875
>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).
LVL 11

Expert Comment

by:Simone B
ID: 38764885
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.

Author Comment

ID: 38764921
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.
LVL 11

Expert Comment

by:Simone B
ID: 38764930
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.
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 800 total points
ID: 38764958
if you are looking to return a value from a dynamic sql check my post here


Author Comment

ID: 38765006
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.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question