Periodic Table Problems - 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 2005 Forums

Transact-SQL (2005)

Periodic Table Problems

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-07-29 : 17:39:29

I have a problem with removing a previous table and establishing a new one of the same name without any trace of the previous iteration remaining. At present, I apply this:

IF EXISTS
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '#Q_TICKET')
DROP TABLE #Q_TICKET

SELECT [event_id], [section_id], [row_id], [seat_num]
INTO [#Q_TICKET]
FROM ALPHA_TX
where event_id = "RU12"
GO

However, I always get this:

Server: Msg 2714, Level 16, State 6, Line 8
There is already an object named '#Q_TICKET' in the database.

(10 row(s) affected)

DELETE TABLE won't work and TRUNCATE TABLE only leaves a ghostie.

How can I simply erase the previous iteration an create anew?

Thanks...

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 18:19:52

temp tables aren't in INFORMATION_SCHEMA.TABLES

if you need to test for the existence of the #temp table before dropping it, do it like this


if object_id('tempdb..#Q_TICKET') is not null
drop table #Q_TICKET

Semper Fi brother!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-07-29 : 18:30:38

The Gunny thanks you, Marine!

BUT.....what if it isn't a TEMP table?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 23:05:12

Gunny, if it starts with a # then it is a temp table. if it really isn't a temp table then your code will work.

so will mine, if u remove the tempdb reference

if object_id('yourTable') is not null
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources