Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Tables revisited


Temp Tables revisited

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11562
Christian Buettner-167247 (6/24/2013)
Why was this question reposted with the same misleading explanation as the original QOTD a month ago?

If you are refering to the mistake in the last sentence (that I commented on in a previous post), then I think "misleading" is a bit harsh.
If you are refering to something else, I must have overlooked it. And I don't have the time to go back over the QotDs of the last weeks, so maybe you can help me refresh my memory?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hugo Kornelis (6/24/2013)
Christian Buettner-167247 (6/24/2013)
Why was this question reposted with the same misleading explanation as the original QOTD a month ago?

If you are refering to the mistake in the last sentence (that I commented on in a previous post), then I think "misleading" is a bit harsh.
If you are refering to something else, I must have overlooked it. And I don't have the time to go back over the QotDs of the last weeks, so maybe you can help me refresh my memory?

To make the story short: You cannot create a temporary table more than once in the same batch. It doesnt matter if you have control flow logic or not.
CREATE TABLE #Test (a INT)
DROP TABLE #Test
CREATE TABLE #Test (a INT)
DROP TABLE #Test
GO


You will get the same error that the table already exists.
More important - if you use regular tables instead, the query works just fine.

Best Regards,

Chris Büttner
call.copse
call.copse
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2843 Visits: 1859
sharath.chalamgari (6/24/2013)
what could be the better approach in this kind of situation , is there any better option than this

...


Personally I'd suggest that if you are creating two temp tables with different structures they are serving different purposes. So name them differently according to their purposes e.g. CREATE TABLE #FullDesc and CREATE TABLE #ShortDesc in the case of the original question. It's not like you could query the two tables interchangeably. Suitable modifications to the subsequent control structure would depend on what you are doing.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11562
Christian Buettner-167247 (6/24/2013)
More important - if you use regular tables instead, the query works just fine.

Hmmm, that's a surprise to me! I thought you'd get the same problem when using permanent tables. So there is, apparently, much more to this than I first though.
Thanks for putting me straight! Wink


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
PurpleLady
PurpleLady
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4796 Visits: 967
Definitely have to slow down and read ALL the answersSmile



Mike Is Here
Mike Is Here
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 513
I really didn't care for this question, you must fix all errors regardless of the order they appear.

I selected the wrong answer with the User error, which was half right.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
sharath.chalamgari (6/24/2013)
http://support.microsoft.com/kb/295305

The author should have included that reference in the explanation instead of guessing at how parsing works (and getting it wrong).



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 2342
Nice discussion even if the original explanation was a bit flawed. Thanks for the question Sergiy.
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hugo Kornelis (6/24/2013)
[...]Thanks for putting me straight! Wink
Wow, give me a minute - I need to put this into a pricture frame for my living room (or my next résumé);-)

Best Regards,

Chris Büttner
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12002
Paul White (6/24/2013)
sharath.chalamgari (6/24/2013)
http://support.microsoft.com/kb/295305

The author should have included that reference in the explanation instead of guessing at how parsing works (and getting it wrong).

+ rather more than just 1

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search