Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Temp Tables revisited Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 6,159, Visits: 8,420
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
Post #1466618
Posted Monday, June 24, 2013 2:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #1466629
Posted Monday, June 24, 2013 2:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:08 AM
Points: 1,808, Visits: 1,192
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.
Post #1466634
Posted Monday, June 24, 2013 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 6,159, Visits: 8,420
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! ;)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1466635
Posted Monday, June 24, 2013 5:02 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:30 AM
Points: 4,123, Visits: 761
Definitely have to slow down and read ALL the answers:)


Post #1466685
Posted Monday, June 24, 2013 6:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:20 PM
Points: 1,400, Visits: 486
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.
Post #1466708
Posted Monday, June 24, 2013 7:03 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:45 AM
Points: 9,928, Visits: 11,208
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1466725
Posted Monday, June 24, 2013 7:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 26, 2014 9:42 AM
Points: 3,430, Visits: 2,039
Nice discussion even if the original explanation was a bit flawed. Thanks for the question Sergiy.
Post #1466732
Posted Monday, June 24, 2013 8:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Hugo Kornelis (6/24/2013)
[...]Thanks for putting me straight! ;)
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
Post #1466774
Posted Monday, June 24, 2013 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 7,935, Visits: 9,662
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
Post #1466799
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse