There is already an object named '#Test' in the database

  • okbangas

    SSChampion

    Points: 11773

    If I run the following query:

    if cast(GETDATE() as time) > '12:00:00'

    select GETDATE() into #Test

    else

    select GETDATE()-0.5 into #Test;

    I get the following error message:

    Msg 2714, Level 16, State 1, Line 4

    There is already an object named '#Test' in the database.

    Why is this so? The two cases of the if statement is mutually exclusive, so there is no way that both will be created. Does anyone know why this statement won't run? To me this seems like a bug.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Because the parser doesn't execute conditional logic (just like the optimiser doesn't). The parser just sees two statements that can create the same table and that's an error. Simplistic, yes, but it's the same kind of reason this fails

    CREATE TABLE Test1 (

    ID int

    )

    GO

    ALTER TABLE Test1 ADD SomeColumn Varchar(10)

    SELECT ID, SomeColumn FROM Test1

    Msg 207, Level 16, State 1, Line 3

    Invalid column name 'SomeColumn'.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jason Selburg

    SSC-Insane

    Points: 24637

    Someone else will have to speak the the specifics of "WHY", but I believe it has something to do with the parser having to evaluate both conditions of the IF.

    This is the workaround ...

    CREATE TABLE #test (dt DATETIME)

    IF CAST(GETDATE() AS TIME) > '12:00:00'

    INSERT #test

    SELECT GETDATE()

    ELSE

    INSERT #test

    SELECT GETDATE() - 0.5

    SELECT * FROM #test

    DROP TABLE #test

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • okbangas

    SSChampion

    Points: 11773

    Thanks both of you. Nice to know the bits and pieces about how things work. I'll have to explicitly create the table then.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • okbangas

    SSChampion

    Points: 11773

    Gail: Don't you ever work? It seems that your response time in this forum is pretty impressive 😀



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004484

    okbangas (1/13/2012)


    Gail: Don't you ever work? It seems that your response time in this forum is pretty impressive 😀

    Supposed to be working now. Trying to figure out XPath query. Forums distracting....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    GilaMonster (1/13/2012)


    okbangas (1/13/2012)


    Gail: Don't you ever work? It seems that your response time in this forum is pretty impressive 😀

    Supposed to be working now. Trying to figure out XPath query. Forums distracting....

    That's why I love BI work with reports regularly running around 1-2 minutes. Always enough time for a little peak here :hehe:.

  • matzer

    SSCrazy Eights

    Points: 9727

    okbangas (1/13/2012)


    I'll have to explicitly create the table then.

    Not necessarily.

    select

    GETDATE() - CASE WHEN cast(GETDATE() as time) > '12:00:00' THEN 0 ELSE 0.5 END [SomeDate]

    into

    #Test;

    Edit: reversed THEN and ELSE part

  • okbangas

    SSChampion

    Points: 11773

    Peter Brinkhaus (1/13/2012)


    okbangas (1/13/2012)


    I'll have to explicitly create the table then.

    Not necessarily.

    My real life scenario is far more complex than this sample code, whose sole purpose were to demonstrate what caused the error message, so I have to create the table on before hand. 🙂



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • spaghettidba

    SSC Guru

    Points: 105673

    okbangas (1/13/2012)


    so I have to create the table on before hand. 🙂

    Or use dynamic SQL. That would work as well.

  • okbangas

    SSChampion

    Points: 11773

    Gianluca Sartori (1/13/2012)


    okbangas (1/13/2012)


    so I have to create the table on before hand. 🙂

    Or use dynamic SQL. That would work as well.

    Thought of that one too, but don't like the idea of dynamic SQL being generated within dynamic SQL, that is kinda ugly 🙂



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

Viewing 11 posts - 1 through 11 (of 11 total)

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