[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

649 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