SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Tables revisited


Temp Tables revisited

Author
Message
Hugo Kornelis
Hugo Kornelis
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: 10815 Visits: 11967
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3541 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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3786 Visits: 1921
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
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: 10815 Visits: 11967
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.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

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



Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15520 Visits: 11354
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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5040 Visits: 2371
Nice discussion even if the original explanation was a bit flawed. Thanks for the question Sergiy.
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3541 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 (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14192 Visits: 12197
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