Reputation: 65

Getting "There is already an object named" while creating a temp table

Msg 2714, Level 16, State 1, Procedure QOTD, Line 12 There is already an object named '#tmpID3' in the database.

ALTER PROCEDURE QOTD (@source INT) AS 
BEGIN 
  IF @source = 1 
    SELECT ID INTO #tmpID3 FROM tbl1 
  ELSE 
    SELECT ID INTO #tmpID3 FROM tbl2 

  SELECT ID FROM #tmpID3 

  DROP TABLE #tmpID3 
END

Msg 2714, Level 16, State 1, Procedure QOTD, Line 7 There is already an object named '#tmpID3' in the database. – jesvin Nov 19 at 5:37

while adding this i am getting the error

Upvotes: 0

Answers (6)

SharpC

Reputation: 7474

I had exactly the same issue, see my answer here:
There is already an object named '#columntable' in the database

The solution in this case seems to be to first create the table, then add the rows. This way the parser does not complain (as this is a known parser issue).

ALTER PROCEDURE QOTD (@source INT) AS 
BEGIN
  -- Create the table without having to declare any column types or sizes
  SELECT TOP 0 ID INTO #tmpID3 FROM tbl1

  -- Prevent IDENTITY_INSERT error
  SET IDENTITY_INSERT #tmpID3 ON

  -- Add the actual rows required
  IF @source = 1 
    INSERT INTO INTO #tmpID3 (ID) SELECT ID FROM tbl1
  ELSE 
    INSERT INTO INTO #tmpID3 (ID) SELECT ID FROM tbl2

  SET IDENTITY_INSERT #tmpID3 OFF

  SELECT ID FROM #tmpID3 

  DROP TABLE #tmpID3 
END

Upvotes: 0

Tony

Reputation: 10357

You are seeing a parser error when trying to create your procedure. The temp table does not yet exist but the parser thinks it does.

Have a look at my answer to this question: There is already an object named '#columntable' in the database.

I originally thought, as others who have answered your question, that you would get this error because you were not explicitly dropping the temp table at the end of you procedure. However, as crokusek first pointed out in his comment:

local temp tables are auto deleted at the end of the procedure in which they are created

So I tried creating your procedure in my SQL Server 2008 instance and got the same error.

Changing the procedure to use different temp table names, as shown below, avoids the problem and proves the temp tables are dropped after the procedure ends.

CREATE TABLE tbl1 ( ID INT )
GO
CREATE TABLE tbl2 ( ID INT )
GO
INSERT INTO tbl1(ID) VALUES (1),(2),(3)
INSERT INTO tbl2(ID) VALUES (4),(5),(6)
GO
CREATE PROCEDURE QOTD ( @source INT )
AS 
    SET NOCOUNT ON
    BEGIN 
        IF @source = 1 
            BEGIN
                SELECT  ID INTO #tmpID13 FROM tbl1
                SELECT  ID FROM #tmpID13
            END
        ELSE 
            BEGIN 
                SELECT  ID INTO #tmpID23 FROM tbl2
                SELECT  ID FROM #tmpID23
            END
    END 
GO
EXEC QOTD 1
EXEC QOTD 2

Output:

ID
-----------
1
2
3

ID
-----------
4
5
6

Upvotes: 3

Adriaan Stander

Reputation: 166606

Have a look at Check If Temporary Table Exists

EDIT How to check for the temp table and drop it if it exists

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
BEGIN
    DROP TABLE #TEMP
END

EDIT 2 It would seem that the 2 select into's are conflicting with each other. Creating the table before hand works though. Something like

ALTER PROCEDURE QOTD (@source INT) 
AS 
BEGIN 
    IF OBJECT_ID('tempdb..#tmpID3') IS NOT NULL 
    BEGIN 
        DROP TABLE #tmpID3 
    END
    CREATE TABLE #tmpID3(
            ID INT
    )
    IF @source = 1 
    BEGIN
        INSERT INTO #tmpID3 SELECT ID FROM tbl1 
    END
    ELSE 
    BEGIN
        INSERT INTO #tmpID3 SELECT ID FROM tbl2
    END
    SELECT ID FROM #tmpID3 
    DROP TABLE #tmpID3 
END

EDIT 3 the temp table is not required in this instance. A simple if will do

Something like

ALTER PROCEDURE QOTD (@source INT) 
AS 
BEGIN 
    IF @source = 1 
    BEGIN
        SELECT ID FROM tbl1 
    END
    ELSE 
    BEGIN
        SELECT ID FROM tbl2
    END
END

Upvotes: 0

APC

Reputation: 146349

Objects have to have unique names across the database. SQL Server handles the uniqueness of temporary table names. However, if there are supplementary objects, such as separately created primary keys, it is possible for collisions to occur when two users attempt to create the table at the same time.

The error message you cite has an object with ID in its name, so I am guessing this is the situation you find yourself in. Andy Novick has written a note on this topic, explaining why it might happen and giving a couple of workarounds. Check it out.

Upvotes: 0