Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: HOWTO drop temporary table ?

    hi, i have one temporary table

    #tmp_tbl

    before i create a temporary table , i would like to drop it first, but i try

    "drop table if exists #tmp_tbl "

    it doesn't work , any help ? thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    a temp table will not exists in a thread that just got created...so there is no need to do a drop

    Do this:

    Code:
    USE Northwind
    CREATE TABLE #myTemp99 (Col1 int)
    USE tempdb
    SELECT * FROM sysobjects
    USE Northwind
    DROP TABLE #myTemp99
    GO
    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.

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    Try this code if you would like to test if an object is present and needs to be dropped.

    IF OBJECT_ID('dbo.temp_MyTable') IS NOT NULL DROP TABLE dbo.temp_MyTable

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That method does not work on temporary tables. They exist in the temp database, under a name that indicates the connection to which they belong. So just trying to drop them by the name used to create them will fail.
    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
  •