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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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)
    Intro to Tally Tables and Functions

  • 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