Drop table not working

Question

Drop table not working

tshad

SSCertifiable

Points: 5905
More actions
May 28, 2014 at 7:06 pm

#289309

I have a store procedure that creates a temp table #NewEmployees.
I then drop the table and recreate it in a Select/Into clause but I get the error:
There is already an object named '#NewEmployees' in the database.
It was already in the database which I had dropped and recreated in a Select/Into earlier in the procedure but now I wanted to drop it and recreate it.
The code is:
DROP TABLE #NewEmployees
SELECT ves.*,VantageStatusID, ehr.EmployeeID
INTO #NewEmployees
FROM Import.VEmployeeStaging ves
JOINEmployee ehr
ONehr.SSN = ves.SSN
Can you not do this?
Thanks,
Tom

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Check out the Code sample I have in this article http://jasonbrimhall.info/2014/05/23/can-you-partition-a-temporary-table/

You'll see that I drop the temp table at the beginning if it exists. I run this code over and over and over and it works fine.

I have seen it fail occasionally if changing the definition of the temp table. A manual drop of the temp table fixes that though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 2

Quick thought, as the table is in tempdb, change the statement to

DROP TABLE tempdb..#NewEmployees

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Eirikur Eiriksson (5/28/2014)
Quick thought, as the table is in tempdb, change the statement to
DROP TABLE tempdb..#NewEmployees

Unnecessary. The # designates it as a temp table, temp tables are always in TempDB. If you do run that, you get a message

Database name 'tempdb' ignored, referencing object in tempdb.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

tshad (5/28/2014)
I have a store procedure that creates a temp table #NewEmployees.
I then drop the table and recreate it in a Select/Into clause but I get the error:
There is already an object named '#NewEmployees' in the database.

If I'm not mistaken, that's a parse-time error. At parse time the temp table exists and hence the parser interprets the command to create it as an error. The parser doesn't execute the drop, it's just a parser.

At run-time, the drop will actually run, but the error comes far earlier.

Easiest would just be to use different temp table names unless there's something that requires you to reuse exactly the same name for a different table definition.

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 5

Quick question, are the procedure executions within the same session?

tshad SSCertifiable Points: 5905 More actions · Answer 6

Yes, they are in the same session. As a matter a fact, if I press the debug button, go to the first line of the procedure and cancel the debug, then run it - it works fine.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It's because batches are parsed as a whole, then executed. When the parser hits the second create for that table it fails. It works with the debug break because that breaks it into two batches.

Is there any reason to need to reuse that exact same table name? If it absolutely has to be the same table name, then this will be hell to fix because stored procedures are a single batch (you can't have GO in one) and will probably involve silly, tedious and hard-to-maintain workarounds.

p.s. it doesn't matter whether the procedure executions were in the same session or different sessions. This error comes from dropping and recreating a temp table in a single batch (stored proc).

Distilled down to the basics, this fails:

CREATE PROCEDURE Test

AS

CREATE TABLE #t (Col1 int);

DROP TABLE #t

SELECT 1 AS Col2 INTO #t

GO

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 8

GilaMonster (5/29/2014)
It's because batches are parsed as a whole, then executed. When the parser hits the second create for that table it fails. It works with the debug break because that breaks it into two batches.
Is there any reason to need to reuse that exact same table name? If it absolutely has to be the same table name, then this will be hell to fix because stored procedures are a single batch (you can't have GO in one) and will probably involve silly, tedious and hard-to-maintain workarounds.
p.s. it doesn't matter whether the procedure executions were in the same session or different sessions. This error comes from dropping and recreating a temp table in a single batch.

Just to clarify and please correct me if I'm wrong here, but the temporary table would be in a scope of a session, not of a batch, regardless of parsing errors. A temporary table with the same name cannot be created within the same session unless it has been dropped in a previous batch.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Eirikur Eiriksson (5/29/2014)
Just to clarify and please correct me if I'm wrong here, but the temporary table would be in a scope of a session, not of a batch, regardless of parsing errors.

But this is a parsing error. It's not an execution error, it occurs at parse time because you're trying to create a temp table that the parser registers as already having been created. (and no, temp tables aren't session-scoped always, and you can create a temp table with the same name multiple times in a single session, as long as they are in different scopes )

Run the create procedure I just added to my previous comment and you'll notice that the create procedure fails. That's a parsing error.

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 10

Forgive me for being slow here and slightly off the topic, but how can a single session, multiple batch thingy create multiple instances of a temporary table with the same name?

As an example, this statement fails

use tempdb;

go

select 1 as val into #tmp01

go

select 1 as val into #tmp01

go

drop table #tmp01

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

I said different scopes, not different batches. The batch is the unit of parsing (hence causing the OP's problem)

CREATE PROCEDURE T1 AS

CREATE TABLE #Temp1 (Col1 int)

IF @@NESTLEVEL <= 10

EXEC T1

IF @@NESTLEVEL = 10

SELECT * FROM tempdb.sys.tables AS t WHERE name LIKE '#Temp1%'

GO

CREATE TABLE #Temp1 (Col2 int)

EXEC T1;

DROP TABLE #Temp1

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

Drop table not working

Cookies on SQLServerCentral