Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop Temp Table If Exists Expand / Collapse
Author
Message
Posted Monday, April 9, 2012 3:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 7, 2014 7:14 AM
Points: 36, Visits: 584
My question is why this code not working
I want to check if global temp table exist if no then recreate it
If yes then drop table and then create it.

This table not exists in temp db for the first time.


IF object_id('TEMPDB.DBO.##test') IS NULL
begin

create table ##test (
counted int
)

end

else
begin
drop table ##test
create table ##test (
counted int
)
end



But – sql complain:
“There is already an object named '##test' in the database.” (Table not realy exist)
What the problem with this logic ?
Thank a lot sharon
Post #1280064
Posted Monday, April 9, 2012 3:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 23, Visits: 379
This indeed is a very odd and strange behavior of SQL. You cannot add the definition of the temp table (CREATE TABLE #...) again in the same batch as it is validated while parsing the batch just like the declared variables. But unlike variables they are created only at runtime. You can get around this by having a different name for the temp tables OR a workaround may be possible if you can give details about your problem.
Post #1280067
Posted Monday, April 9, 2012 4:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 23, Visits: 379
Also, since you are using global temporary tables, you can create two procedures which would check the temp table existence and create the desired temp table with different definition. And based upon the IF ELSE logic, you can execute the related procedure. This way you would be able to access the global temp table.
Post #1280068
Posted Monday, April 9, 2012 5:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 601, Visits: 4,981
Try this:

IF object_id('TEMPDB.DBO.##test') IS NOT NULL drop table ##test
create table ##test
(counted int)

Post #1280080
Posted Monday, April 9, 2012 5:48 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 601, Visits: 4,981
I would also recommend not using a global temp table. There are some edge cases where they might be necessary but as a general rule of thumb I would limit temp tables to the current session.
Post #1280082
Posted Monday, April 9, 2012 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 23, Visits: 379
jewel.sacred (4/9/2012)
Also, since you are using global temporary tables, you can create two procedures which would check the temp table existence and create the desired temp table with different definition. And based upon the IF ELSE logic, you can execute the related procedure. This way you would be able to access the global temp table.


Aghhhh... I missed the whole thing. I thought the user needed to add the temp table with different definitions. Never thought that the OP was after such a simple thing

But anyhow, I hope my response would let the OP knows why the error was generated with that code
Post #1280084
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse