Dynamic EXEC and Temporary Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic EXEC and Temporary Tables

Author

Topic

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-29 : 19:44:22

Hello

Im trying to execute an dynamicly SQL query with EXEC into a temporary table.

EXEC('SELECT ' + @turboSQL + ' INTO #TempTable FROM sweety')

SELECT * FROM #TempTable -- I get an error on this one
saying that the #TempTable doesnt exist?

Anyone got a workaround on this?.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-29 : 19:57:12

DDL
CREATE TABLE #TempTable.....

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-29 : 20:04:54

raclede, that's not a helpful answer.

natas, what you need to do is create the temp table first, otherwise it goes out of scope when your exec has finished.

i.e.



Create Table #TempTable (
 blah varchar(10)
)

Exec ('insert into #TempTable SELECT ' + @turboSQL)

Select * From #TempTable

What this means is you need to know what columns the temp table will have beforehand.

Damian
Ita erat quando hic adveni.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-29 : 20:25:14

well That is what I meant.. create first the temp table..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-29 : 20:32:31

Ah man

The @turboSQL varchar(8000) value contains some good CASE WHEN statements, im doing a dynamic PIVOT u know on a table that i dont beforehand know how many rows/columns it has, do i HAVE TO create the table structure first?. if i HAVE TO do it i have to create the temptable with a dynamic "CREATE" statement also, Ah man, this is to much

, cant it be solved some other way?.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-29 : 20:34:31

quote:
Originally posted by raclede

well That is what I meant.. create first the temp table..

It might be what you meant, but you didn't communicate that.

Damian
Ita erat quando hic adveni.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-29 : 20:36:42

quote:
Originally posted by natas

do i HAVE TO create the table structure first?.

Yes, pretty much.
Maybe you can create a temp table to suit every outcome then only use the ones you need.

If you give some more info about what you are trying to achieve maybe we can help some more. You mentioned pivot tables, have you read Jeff's blogs and Rob's article ?

Damian
Ita erat quando hic adveni.

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-29 : 20:47:59

Thanks guys for your quick replys.

The "raw" table look like this:

COlumns: customerID,index,question,answer

78,0, Question0, Answer0
78,1, Question1, Answer1
78,2, Question2, Answer2

79,0, Question0, Answer0
79,1, Question1, Answer1
79,2, Question2, Answer2
79,3, Question3, Answer3
79,4, Question4, Answer4
79,5, Question4, Answer5

and so on and so on... . .
Question and answers are real questions and answers from the customer... . . ..bla bla..

i take this rowset and place it in a TempTable.
I then i

SELECT @maxindex=MAX(index) FROM #TempTable

and then use a:

WHILE(@counter<@maxindex+1)
BEGIN
@turboSQL=@turboSQL + 'MAX(CASE WHEN index=' + @counter + ' THEN Question END)'
@turboSQL=@turboSQL + 'MAX(CASE WHEN index=' + @counter + ' THEN Answer END)'
END

to build up the dynamic PIVOT

and then use

EXEC('SELECT ' + @turboSQL + ' INTO #TempTable2 FROM #TempTable')

and then do some good AGGREGATES on #TempTable2

SELECT COUNT(DISTINCT *),* FROM #TempTable2

voila!... (not)

good stuff

But now, i would like to do some AGGREGATES on this PIVOT Table, adn i thought placing it in a second temptable would be a supergood idea, but it wasnt apparently

, i only want to calculate dublicate rows, thats all i want..

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-29 : 21:30:33

I found this article
http://www.sqlteam.com/item.asp?ItemID=2955

To create a temporary table inside a EXEC, you just simply add an extra # like this:

EXEC('SELECT ' + @turboSQL + ' INTO ##TempTable2 FROM #TempTable')

That solved it. Thanks anyway dudes

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-29 : 22:42:43

Yeah, that will do it. That is a global temp table, which is available to all users, so you want to make sure that two people won't be viewing it at once.

Another thing that I have done in the past, is create the temp table outside of the dynamic sql, then built up some sql to alter the table according to my data.

Damian
Ita erat quando hic adveni.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources