Temp table not droping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Temp table not droping

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-08 : 11:41:56

I get the error:
Msg 2714, Level 16, State 1, Line 6
There is already an object named '#A' in the database.

When I run:


    IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
    CREATE TABLE #A (C1 INT);
    INSERT INTO #A (C1) VALUES(1);
    SELECT * FROM #A; 
    IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
    CREATE TABLE #A (C2 INT);
    INSERT INTO #A (C2) VALUES(2);
    SELECT * FROM #A;

If I run the IF OBJECT_ID code by itself it drops the table, however if I run as shown it does not.

What am I missing?

Please note that I have tried using IF EXISTS with the same results.

djj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 12:46:30

Thats because they're in same batch and when you try to compile itself analyser checks and sees both the create statements which causes the error.

try this


IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
    CREATE TABLE #A (C1 INT);
    INSERT INTO #A (C1) VALUES(1);
    SELECT * FROM #A; 
    GO
    IF OBJECT_ID('tempdb..#A') IS NOT NULL 
    DROP TABLE #A;
    CREATE TABLE #A (C2 INT);
    INSERT INTO #A (C2) VALUES(2);
    SELECT * FROM #A;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-08 : 13:10:06

Thank you for the reply. The GO if placed after the second IF..DROP works.
So if I have

IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
    CREATE TABLE #A (C1 INT);
    INSERT INTO #A (C1) VALUES(1);
    SELECT * FROM #A; 
    GO
    IF OBJECT_ID('tempdb..#A') IS NOT NULL 
    DROP TABLE #A;
    
    CREATE TABLE #A (C2 INT);
    SELECT * FROM #A;

This will work and show C2 as the column name.
However if I have:

IF OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;
    CREATE TABLE #A (C1 INT);
    INSERT INTO #A (C1) VALUES(1);
    SELECT * FROM #A; 
    GO
    IF OBJECT_ID('tempdb..#A') IS NOT NULL 
    DROP TABLE #A;
    
    CREATE TABLE #A (C2 INT);
    SELECT * FROM #A;

    INSERT INTO #A (C2) VALUES(2);

It fails on the insert.

I can add the GO but this will not work in a stored procedure.

djj

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 13:25:53

What aer you trying to do? Why not simply truncate/delete the rows if you need to rather than trying to drop and recreate the table?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-08 : 13:48:25

What I was doing is renaming a column but keeping the same table name. The easy way would be to use a different table name, but I was wondering why the error in the first place.

I assume as visakh16 said it has to do with the compile.

djj

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 13:51:33

To my simple-minded way of thinking, this behavior indeed is a deficiency in the parser. The parser is trying to be too helpful and tripping up in the process.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-08 : 14:08:06

Yeah, I'm not sure of the extract reason fot this either (scoping, persistance, op[timization, etc???). But, BOL is pretty clear that you cannot:

quote:
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources