asked on

Error: There is already an object named '#SomeTempTable' in the database

Hi Experts!

So, we've CI / CD for our database development.

Below is a huge script merged from two seperate scripts.
Lines 1-18 is script 1 and 20-36 is script 2.

Weird thing is that at line 27 it would error out:

Msg 2714, Level 16, State 1, Line 27
There is already an object named '#SomeTempTable' in the database.

Any ideas?

    IF OBJECT_ID('Tempdb..#SomeTempTable') IS NOT NULL 
    begin
    select 'IN :' + convert(varchar(10), getdate(), 101)
    DROP TABLE #SomeTempTable
    end
    else      select 'Out :'

    CREATE TABLE #SomeTempTable (
        [RecId] NUMERIC(18, 0) IDENTITY(1,1)
    ,   [Action] VARCHAR(10)
    )

    INSERT INTO #SomeTempTable ([Action])
    VALUES ('INSERT')

--select * from #SomeTempTable
/*Script 1 does something */


    IF OBJECT_ID('Tempdb..#SomeTempTable') IS NOT NULL 
    begin
    select 'IN :' + convert(varchar(10), getdate(), 101)
    DROP TABLE #SomeTempTable
    end
    else select 'Out :'

    CREATE TABLE #SomeTempTable (
        [RecId] NUMERIC(18, 0) IDENTITY(1,1)
    ,   [Action] VARCHAR(10)
    )

    INSERT INTO #SomeTempTable ([Action])
    VALUES ('UPDATE')

--select * from #SomeTempTable
/*Script 2 does something  else*/

Open in new window

ASKER CERTIFIED SOLUTION