SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop Temp Table If Exists


Drop Temp Table If Exists

Author
Message
sharon-472085
sharon-472085
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 873
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
jewel.sacred
jewel.sacred
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 625
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.
jewel.sacred
jewel.sacred
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 625
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.
Charles Hearn
Charles Hearn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 5155
Try this:


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


Charles Hearn
Charles Hearn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 5155
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.
jewel.sacred
jewel.sacred
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 625
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 Hehe

But anyhow, I hope my response would let the OP knows why the error was generated with that code :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search