Deletion\Creation of Temp tables in SQL Server 2008

Question

I have SQL code like this

IF Object_id('tempdb..#empDate) IS NOT NULL
  DROP TABLE #empDate
CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )

After the above code some more lines of SQL follow and then it is repeated.

I get the following error.

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

I replaced the

IF Object_id('tempdb..#empDate) IS NOT NULL

with

IF Object_id('tempdb..#empDate%) IS NOT NULL

As it is written on the forums that SQL Server appends number to the subsequent temp table(s).

Source: Check if a temporary table exists and delete if it exists before creating a temporary table

http://blog.sqlauthority.com/2009/05/17/sql-server-how-to-drop-temp-table-check-existence-of-temp-table/

http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/

I am using Microsoft SQL Server 2008 on Windows 7 Enterprise.

I am not able to understand the cause of the error.

Please help.

`If EXISTS(Select * From tempdb.dbo.sysobjects Where ID = OBJECT_ID(N'tempdb..#empDate')) Begin Drop Table #empDate End` — Kiril Rusev, Dec 30 '13 at 14:59
Why do you need to check if the table exists twice? You know it exists if you have created it in the same session. You are only using a local temporary table so it can't be affected by other sessions, just skip the second create table statement, or do what M Ali as suggested and run it in multiple batches. — GarethD, Dec 30 '13 at 15:53
Why don't you give a proper question title here? *"Temp tables in SQL Server 2008"* doesn't actually sounds like a problem/question to me!! — huMpty duMpty, Dec 30 '13 at 18:01
@user2560781: Seems much better than earlier :). But don't include tags on the title i.e. `sql-server 2008`. Please read [Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles) — huMpty duMpty, Dec 31 '13 at 10:25

M.Ali · Accepted Answer · 2013-12-30T15:28:03.057

Sample One

This will fail...... Executing the same code again, will throw the error you are getting now

IF Object_id('tempdb..#empDate') IS NOT NULL
 BEGIN
  DROP TABLE #empDate
 END 

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )


IF Object_id('tempdb..#empDate') IS NOT NULL
 BEGIN
  DROP TABLE #empDate
 END 

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )

Sample Two (Fixed)

IF Object_id('tempdb..#empDate') IS NOT NULL
 BEGIN
  DROP TABLE #empDate
 END 

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )

GO      --<-- Adding this Batch Separator will eliminate the Error

IF Object_id('tempdb..#empDate') IS NOT NULL
 BEGIN
  DROP TABLE #empDate
 END 

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )

Test

If you try Executing the following Statements in ONE BATCH they will fail even though there isnt any table at all with the name #empDate, it will not even execute the very 1st Create table Statement. and will throw an error.

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )


DROP TABLE #empDate


CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )

But if you separate all the statement in separate batches they will be executed successfully something like this..

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )
GO

DROP TABLE #empDate
GO

CREATE TABLE #empDate
  (
     [empID]   INT,
     [AddLoc] VARCHAR(1000)
  )
GO

score 0 · Answer 2 · answered Dec 30 '13 at 14:59

I would just drop your table without any pre-checks.

Then write/run the script clean.

Once done using the temp table, drop it at the end of your script.

So run this unconditionally

DROP TABLE #empDate

Then write/run your script and make sure you have this line at the end of your script.

score 0 · Answer 3 · answered Dec 30 '13 at 15:07

pass database name with object_id

example :

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL 
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO

Deletion\Creation of Temp tables in SQL Server 2008

3 Answers3