SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Temp table not droping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 07/08/2013 :  11:41:56  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/08/2013 :  12:46:30  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 07/08/2013 :  13:10:06  Show Profile  Reply with Quote
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

Edited by - djj55 on 07/08/2013 13:11:00
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 07/08/2013 :  13:25:53  Show Profile  Reply with Quote
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?
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 07/08/2013 :  13:48:25  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 07/08/2013 :  13:51:33  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 07/08/2013 :  14:08:06  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 07/08/2013 14:10:44
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000