Temporary table in SQL server causing ' There is already an object named' error

Question

I have the following issue in SQL Server, I have some code that looks like this:

DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN  FROM TBL_PEOPLE

When I do this I get an error 'There is already an object named '#TMPGUARDIAN' in the database'. Can anyone tell me why I am getting this error?

Solution 1

You are dropping it, then creating it, then trying to create it again by using SELECT INTO. Change to:

DROP TABLE #TMPGUARDIAN
CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

INSERT INTO #TMPGUARDIAN 
SELECT LAST_NAME,FRST_NAME  
FROM TBL_PEOPLE

In MS SQL Server you can create a table without a CREATE TABLE statement by using SELECT INTO

Solution 2

I usually put these lines at the beginning of my stored procedure, and then at the end.

It is an "exists" check for #temp tables.

IF OBJECT_ID('tempdb..#MyCoolTempTable') IS NOT NULL
begin
        drop table #MyCoolTempTable
end

Full Example:

CREATE PROCEDURE [dbo].[uspTempTableSuperSafeExample]
AS
BEGIN
    SET NOCOUNT ON;


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


    CREATE TABLE #MyCoolTempTable (
        MyCoolTempTableKey INT IDENTITY(1,1),
        MyValue VARCHAR(128)
    )  

    INSERT INTO #MyCoolTempTable (MyValue)
        SELECT LEFT(@@VERSION, 128)
        UNION ALL SELECT TOP 10 LEFT(name, 128) from sysobjects

    SELECT MyCoolTempTableKey, MyValue FROM #MyCoolTempTable


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


    SET NOCOUNT OFF;
END
GO

Solution 3

You must modify the query like this

CREATE TABLE #TMPGUARDIAN(
LAST_NAME NVARCHAR(30),
FRST_NAME NVARCHAR(30))  

INSERT INTO #TMPGUARDIAN(FRST_NAME,LAST_NAME)
SELECT LAST_NAME,FRST_NAME  FROM TBL_PEOPLE

-- Make a last session for clearing the all temporary tables. always drop at end. In your case, sometimes, there might be an error happen if the table is not exists, while you trying to delete.

DROP TABLE #TMPGUARDIAN

Avoid using insert into Because If you are using insert into then in future if you want to modify the temp table by adding a new column which can be filled after some process (not along with insert). At that time, you need to rework and design it in the same manner.

Use Table Variable http://odetocode.com/articles/365.aspx

declare @userData TABLE(
 LAST_NAME NVARCHAR(30),
    FRST_NAME NVARCHAR(30)
)

Advantages No need for Drop statements, since this will be similar to variables. Scope ends immediately after the execution.

Solution 4

Some times you may make silly mistakes like writing insert query on the same .sql file (in the same workspace/tab) so once you execute the insert query where your create query was written just above and already executed, it will again start executing along with the insert query.

This is the reason why we are getting the object name (table name) exists already, since it's getting executed for the second time.

So go to a separate tab to write the insert or drop or whatever queries you are about to execute.

Or else use comment lines preceding all queries in the same workspace like

CREATE -- …
-- Insert query
INSERT INTO -- …

View more solutions

Comments

[Johannes Wentu]

In my case it seems it's not enough. The mere fact that I am write twice in the same select the same "SELECT MyField INTO #TempTable" is enough to cause the compilation error. The two select into cannot be hit at the same time because they are in different branches of a IF THEN ELSE: nonetheless, i can't write twice the same SELECT INTO

[granadaCoder]

Don't use Select-Into. Its a "short cut". Create the #tempTable, use the "Insert Into #MyTemp Select Col1, Col2 from dbo.MyTable" syntax.

[AMRESH PANDEY]

IF OBJECT_ID('tempdb..#TMPGUARDIAN') IS NOT NULL begin drop table #TMPGUARDIAN end Select LAST_NAME,FRST_NAME INTO #TMPGUARDIAN from TBL_PEOPLE SELECT * FROM #TMPGUARDIAN

[RBerman]

Amresh, that doesn't work in this case - as noted elsewhere, even when the select-into is in mutually exclusive branches, the mere fact there are two of them into the same temp table will cause a compiler error. It's a deficiency in the lexxer, apparently.

[RBerman]

That "fixed" it because the disconnect deleted the temp table. This has nothing to do with the original question.

[naz786]

I am using SELECT INTO rather than CREATE TABLE, and still getting the error There is already an object named '#MyTempTable' in the database.

[Hart CO]

@naz786 In that case it must already exist, for temp tables you might find it helpful to include a DROP TABLE IF EXISTS #yourTable; before you create.

[naz786]

Yes I tried that but didn't work. Since this SELECT INTO was not working for me, I have resorted to writing two queries: one for creating the table and another for inserting into the table. This was a workaround for me. Thank you anyway. :)