none
temporary table compile

    Question

  • Hi there. Try running the following on SQL Server 2008:


    create
    table ##BANKIMPORTDATA
    (
        DATA varchar(max)
    )

     

    if exists (select * from tempdb.dbo.sysobjects where name = '##BANKIMPORTDATA')
    begin
        drop table ##BANKIMPORTDATA
    end

    create
    table ##BANKIMPORTDATA
    (
        DATA varchar(max)
    )

    You will find that the script will not compile. What I can do is to truncate the table instead of re-creating it. The problem with that though is that, in I my original script (which I will not include here for sake of space,time and security), there is not definite way that I can know whether the temp table exists or not since it is created conditionally (inside a previous cursor), and I have to re-create it in another cursor afterwards, as well as alter it thereafter (which I have also done in the previous cursor). I guess the compiler tries to "help" me, but it's not really helping. Any work-around for this?

    Wednesday, August 26, 2009 6:13 PM

Answers

  • Can you put a GO after the first CREATE TABLE command?  In other words...

    CREATE TABLE...
    GO
    ...
    IF EXISTS...


    That should compile okay.


    --Brad (My Blog)
    • Marked as answer by Zongqing Li Wednesday, September 02, 2009 8:19 AM
    Wednesday, August 26, 2009 6:25 PM
    Moderator

All replies

  • Try below.

    if object_id('tempdb..##BANKIMPORTDATA') is not null
    begin
        truncate table
    tempdb..##BANKIMPORTDATA
    end

    create
    table ##BANKIMPORTDATA
    (
        DATA varchar(max
    )
    )

    Tell me if this works.

    Rohit
    Wednesday, August 26, 2009 6:19 PM
  • Can you put a GO after the first CREATE TABLE command?  In other words...

    CREATE TABLE...
    GO
    ...
    IF EXISTS...


    That should compile okay.


    --Brad (My Blog)
    • Marked as answer by Zongqing Li Wednesday, September 02, 2009 8:19 AM
    Wednesday, August 26, 2009 6:25 PM
    Moderator