Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Unanswered: How can I use temporary table in SQL SERVER 2008 like Oracle?

    Hi, everyone.

    I want to use temporary table in SQL SERVER 2008 with the way in which I can use in Oracle 10g. When I commit, the inserted rows can automatically delete by DBMS.

    create temporary table t (i varchar2(10)) on commit delete rows

    Thank a lot!

  2. #2
    Join Date
    Aug 2006
    Posts
    58
    sorry, I want to add something about the above post.

    after I commit, I do not want to lose the table. That is to say, after commit, the DBMS only delete the inserted rows.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Huh? Please explain in detail what you are trying to do, not in terms of Oracle syntax but in terms of your business rules.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    it's pretty clear...oh, wait you're blind.....

    Oracle has some funky stuff

    a temp table "loses" the data...but also the instance of the table

    DB2 has a global temporary table which does what your suggesting in Oracle...once it's referenced, it becomes "alive" once the thread dies, the data does away...but the "ghost" image of the table lives on....and is only available to the thread when you reference it....

    SQL Server does not have this capability

    So...you just need to CREATE TABLE #temp (cols...)

    When you need it


    Not that big of a deal really
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So you are saying that in Oracle the definition of the table persists after the procedure finishes?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It does in DB2 if it's defined that way

    CREATE GLOBAL TEMPORARY TABLE.....

    That's what it sounds like he's saying in Oracle...
    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.

Posting Permissions

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