Temporary table in a batch

  • Anoo S Pillai

    Mr or Mrs. 500

    Points: 547

    Comments posted to this topic are about the item Temporary table in a batch

  • Ron McCullough

    SSC Guru

    Points: 63877

    Interesting question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Interesting "feature" of SQL Server 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • SQLRNNR

    SSC Guru

    Points: 281252

    Nice question.

    Not sure why someone would try to create the same temp table twice in the same procedure. If needing to use the same temp table then clear it and reinsert. Or just use a new temp table with a new name.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nice question. It's by design.

    Thanks.

    Regards,

    Igor

    Igor Micev,
    My blog: www.igormicev.com

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Nice one thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    But the explanation appears to have swallowed the good old party line: sometimes (and it seems reasonably certain that this is one of those times) "it's by design" means "someone made a mess of this, but there's not much point in fixing it so we'll pretend that's what was intended". I've watched that happen in standard writing as well as in code writing and in requirements writing; sometimes the result is pretty harmless, as here, but at other times it is far from harmelss - but the culprits still spout the "it's by design" mantra.

    As for

    explanation


    Books online states it vaguely as

    “If more than one temporary table is created inside a single stored procedure or batch, they must have different names.”

    Detailed explnation is available at - http://www.tectalks.com/493/temporary-table-could-not-be-re-created/

    there is nothing at all vague about the BOL statement, and the "Detailed explanation" doesn't say anything that BOL doesn't.

    Tom

  • higgim

    SSCarpal Tunnel

    Points: 4090

    Also the Begin is missing after AS at the start 🙂

  • This was removed by the editor as SPAM

  • Hany Helmy

    SSChampion

    Points: 13488

    L' Eomot Inversé (2/9/2014)


    Nice question.

    But the explanation appears to have swallowed the good old party line: sometimes (and it seems reasonably certain that this is one of those times) "it's by design" means "someone made a mess of this, but there's not much point in fixing it so we'll pretend that's what was intended". I've watched that happen in standard writing as well as in code writing and in requirements writing; sometimes the result is pretty harmless, as here, but at other times it is far from harmelss - but the culprits still spout the "it's by design" mantra.

    As for

    explanation


    Books online states it vaguely as

    “If more than one temporary table is created inside a single stored procedure or batch, they must have different names.”

    Detailed explnation is available at - http://www.tectalks.com/493/temporary-table-could-not-be-re-created/

    there is nothing at all vague about the BOL statement, and the "Detailed explanation" doesn't say anything that BOL doesn't.

    + 1, Agree.

  • Hany Helmy

    SSChampion

    Points: 13488

    bitbucket-25253 (2/8/2014)


    Interesting question

    + 1, yes.

  • free_mascot

    One Orange Chip

    Points: 27168

    Good One.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Ed Wagner

    SSC Guru

    Points: 286988

    Good question. I think this is all about SQL being a declarative language.

  • twin.devil

    SSC-Insane

    Points: 22208

    nice question ... thanks for sharing

  • Hany Helmy

    SSChampion

    Points: 13488

    SQLRNNR (2/9/2014)


    Nice question.

    Not sure why someone would try to create the same temp table twice in the same procedure. If needing to use the same temp table then clear it and reinsert. Or just use a new temp table with a new name.

    It`s just not practical to create the same temp table twice in the same SP, there is no logic to do that, but the question was interesting enough that I had to test it my self as never did that before.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply