Solved

SQL 2008 create dynamically named temp table

Posted on 2013-01-10
12
485 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now