Will temp tables be dropped when Transaction commits?

  • Hi gurus!

    It is been a long time since I asked you for your input to clear my self doubt 🙂

    I would like to know if a temp table created within a transaction will be dropped automatically after a COMMIT (I already know that ROLLBACK will take care of it) or should I issue a DROP statement after the COMMIT?
    Here's my pseudo code: 

    BEGIN TRAN
         BEGIN TRY
                 -- receive all records for today
                CREATE TABLE #Temptable (RecID int, IncomingID int)
                 INSERT ProductionTable OUTPUT inserted.RecordID, CustomerID INTO #TempTable
                 SELECT c1, c2, c3, c4, c5
                 FROM IncomingDTSTable t1
                 WHERE MovementDate = GETDATE()
                 -- now, using our newly created RecIDs, add the details for each record
                INSERT ProductionTable2
                SELECT tbl1.RecID, tbl1.IncomingID, ctl.col1, ctl.col2, ctl.col3...etc
               FROM #temptable tbl1
               JOIN ProductionTableControl ctl on tbl1.RecID = ctl.c40
               COMMIT TRAN
         END TRY
         BEGIN CATCH
                SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()
                ROLLBACK Transaction
        END CATCH

    So, should I leave it as is or must I put the Drop command after the COMMIT statement to get rid of #TempTable?

    Your help is highly appreciated!

    Regards,
    JC

  • A transaction commit will not drop a temp table.  Technically, a rollback doesn't either.  It just "undoes" the create. 🙂
    A temp table is only dropped when the database session ends or when you explicitly drop it.

  • A COMMIT will not drop the temp table.  You need to drop it yourself.

    A ROLLBACK will drop a temp table if it was created within the transaction, but not if it was created prior to the transaction.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you, David and Scott
     Yeah, I got that the rollback just undoes the create.  😉 
    Question answered!

    Thank you, again and have a great day!
    JCR

Viewing 4 posts - 1 through 3 (of 3 total)

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