Post reply

Temporary tables

  • September 14, 2016 at 8:48 am

    #400722

    Dear all,

    If I have a stored procedure creating and filling a temp table like #tempo1. This is using a connection we can call it Conn1

    Then I am executing exactly the same procedure but using a dif connection. Let us call conn2

    Question:

    Because both create and fill a temp table called #tempo1 and because they run at the same time. Can one fill the temp table of the other?

    thank you

  • September 14, 2016 at 9:08 am

    #1900870

    Nope.

    A temporary table is isolated by the connection that created it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 14, 2016 at 9:32 am

    #1900882

    thank you

  • September 14, 2016 at 5:29 pm

    #1900993

    That is unless the temp table starts with two "#" signs, which makes it a "Global Temp Table", and then the answer would be "yes".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 14, 2016 at 5:53 pm

    #1900994

    While probable not quite accurate...the way to think about is this way...when using a single # think of it as being owned by the schema that made it:

    Conn1.temp01

    Conn2.temp01

    When using a double #, think of it as being owned by dbo

    dbo.temp01

    -SQLBill

  • September 15, 2016 at 3:52 pm

    #1901240

    SQLBill (9/14/2016)

    While probable not quite accurate...the way to think about is this way...when using a single # think of it as being owned by the schema that made it:

    Conn1.temp01

    Conn2.temp01

    When using a double #, think of it as being owned by dbo

    dbo.temp01

    -SQLBill

    I'm glad that this works for you, but I hate it, because it's definitely not accurate. Connections are not schemata.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 15, 2016 at 8:49 pm

    #1901259

    SQLBill (9/14/2016)

    While probable not quite accurate...the way to think about is this way...when using a single # think of it as being owned by the schema that made it:

    Conn1.temp01

    Conn2.temp01

    When using a double #, think of it as being owned by dbo

    dbo.temp01

    -SQLBill

    The only problem is - if conn1 is closed before conn2 is established "dbo.temp01" will be gone by the time you're trying to access it from conn2.

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

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