Execs and temporary tables

  • honza.mf

    SSCertifiable

    Points: 5519

    Comments posted to this topic are about the item Execs and temporary tables



    See, understand, learn, try, use efficient
    © Dr.Plch

  • BrainDonor

    SSCoach

    Points: 19193

    That explanation requires expanding slightly, because there is more than one type of temporary table.

    Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

    Steve Hall
    Linkedin
    Blog Site

  • This was removed by the editor as SPAM

  • Skanker

    Hall of Fame

    Points: 3059

    My gut instinct on this was right. Unfortunately I did not listen to this and chose incorrectly. Doh!!:hehe:

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    Good question, thanks.

  • Thomas Abraham

    SSChampion

    Points: 10761

    BrainDonor (7/6/2012)


    That explanation requires expanding slightly, because there is more than one type of temporary table.

    Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

    Thanks for the amplification.

    And thanks to OP for the interesting question.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Dana Medley

    SSCertifiable

    Points: 6764

    Great question and a nice way to end the week. 🙂



    Everything is awesome!

  • LostAccount

    SSCarpal Tunnel

    Points: 4891

    This question made up for yesterday's beating. Glad we ending the week with this one.

  • Cliff Jones

    SSChampion

    Points: 10517

    David Harder (7/6/2012)


    This question made up for yesterday's beating. Glad we ending the week with this one.

    Yes, I agree.

  • Ron McCullough

    SSC Guru

    Points: 63877

    Emphasis (bolding) added by this poster

    BrainDonor (7/6/2012)


    That explanation requires expanding slightly, because there is more than one type of temporary table.

    Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.

    At the completion of the command given in the QOD

    exec ('create table #qotd2 (id int))' follows the above since the exec command completes, that is #qotd2 has gone out of scope.

    To illustrate, the code below has been modified so that the entire sequence of commands is contained within the scope of the exec command.

    exec ('create table #qotd2 (id int)

    alter table #qotd2 add i int

    insert into #qotd2 (i) values (2)

    select * from #qotd2

    drop table #qotd2')

    The above then will return for the SELECT statement the values:

    id i

    NULL 2

    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]

  • mtassin

    SSC-Insane

    Points: 23035

    Always nice to have an easy one on Friday....



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • SQLRNNR

    SSC Guru

    Points: 281210

    mtassin (7/6/2012)


    Always nice to have an easy one on Friday....

    Yes it is.:-D

    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

  • EL Jerry

    SSCertifiable

    Points: 6932

    Nice question, thanks for the back to basics.

    Happy weekend, everyone.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Revenant

    SSC-Forever

    Points: 42467

    SQLRNNR (7/6/2012)


    mtassin (7/6/2012)


    Always nice to have an easy one on Friday....

    Yes it is.:-D

    Count me in. 🙂

  • Ken Wymore

    SSCoach

    Points: 16357

    Nice question to end the week on. Thanks!

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

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