Solved

SQL 2008 create dynamically named temp table

Posted on 2013-01-10
12
521 Views
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
EXEC(@sql)

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

SELECT * FROM @tab

DROP TABLE @tab
****************************************************
When trying to run this in Management Studio, I get....

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

Any ideas?
0
Comment
Question by:drl1
  • 5
  • 4
  • 3
12 Comments
 
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.
0
 
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

0
 
LVL 11

Accepted Solution

by:
Simone B earned 300 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

0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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.

Aneesh
0
 

Author Comment

by:drl1
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.
0
 

Author Comment

by:drl1
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.
0
 
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).
0
 
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.
0
 

Author Comment

by:drl1
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

or

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.
0
 
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.
0
 
LVL 75

Assisted Solution

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

http://sequelserver.blogspot.ca/2007/01/return-value-from-dynamic-sql.html
0
 

Author Comment

by:drl1
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.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 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