Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4

    Unanswered: Problem reusing Temporary Table names

    This morning I was testing reusing a temporary table. In the past I have wanted to delete and reuse a temp table name and receive a error the object already exists. Leading me to just create a second temp table. So today I had a few minutes and thought I would give it a try again. For some reason I am now unable to use GO. What I have read indicates you need a GO to do this.

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near 'GO'.
    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near 'GO'.
    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near 'GO'.
    Msg 2714, Level 16, State 1, Line 18
    There is already an object named '#ControlTable' in the database.
    Code:
    SET @dd = dateadd(week,-17,getdate()) 
    
    IF OBJECT_ID('tempdb.dbo.#ControlTable%') IS NOT NULL
    	DROP TABLE #ControlTable;
    GO;
    
    SELECT * INTO #ControlTable 
    FROM IMBTracing.dbo.Trace_Lookup_Table
    WHERE Table_Ind = 'CT1' and MailDate <= @dd;
    GO;
     
    IF OBJECT_ID('tempdb.dbo.#ControlTable%') IS NOT NULL
    	DROP TABLE #ControlTable;
    GO;
    
    SELECT * INTO #ControlTable 
    FROM IMBTracing.dbo.Trace_Lookup_Table
    WHERE Table_Ind = 'CT1' and MailDate <= @dd;

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    The % sign is what is giving you the problem, I think. There will be no table named with a % sign. You should certainly be able drop the table using a try/catch block. You can also remove the % signs, and the code above should work (at least I don't see any syntax errors)
    Code:
    create table #temp
    (col1 int)
    
    if object_id('tempdb.dbo.#temp') is not null
      print 'hello'
    
    if object_id('tempdb.dbo.#temp%') is not null
      print 'good bye'

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Ahh. After a quick check, I see what the problem is. GO is a batch terminator, and not a bit of SQL syntax. When you have a semi-colon after the GO, it appears that SQL Server interprets this to mean that GO is the name of a stored procedure you are trying to call. Since you would need an EXEC to call the stored procedure, you get the syntax error. Remove the semi-colons after the GOs, and you should be all set.

  4. #4
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    Thank you, I learned something new today. The %sign was copied from an example I saw in here. I believe its a wildcard because temp table can have _ after the name.

Tags for this Thread

Posting Permissions

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