Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Global temp tables in SQL Server

    Hi!

    I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?

    Thanks so much for your help!

    -Parul

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A global temp table is like any other table.
    How would you prevent users from overwriting data in a permanent table?
    A better question is why are you using a global temporary table, and would local temporary tables suffice?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    I am using a global temp table because I have the following:

    Stored Proc A calls Stored Proc B calls Stored Proc C
    (both Stored Proc B and C need global temp tables)

    Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ParulV
    I am using a global temp table because I have the following:

    Stored Proc A calls Stored Proc B calls Stored Proc C
    (both Stored Proc B and C need global temp tables)
    This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    Won't the variables lose scope if local temp tables are used?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    In any case.....


    Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

    Use @@SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2002
    Posts
    74
    That should work, thanks!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?

    Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology

    Simple is usually the best answer
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Oct 2002
    Posts
    74
    while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    In any case.....


    Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

    Use @@SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
    We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Oct 2002
    Posts
    74
    so what else do you recommend i should try?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Local.

    Temporary.

    Tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •