Check if Item Exists? - 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 2000 Forums

Transact-SQL (2000)

Check if Item Exists?

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-25 : 04:48:29

Hi

I was just wondering how can I check if a temp table with a certain name already exists with code.

Here's my situation: I want to create a temp table (vwTemp) but if the table exists then it must drop the table then create it

how can I do this in QA?

You can't teach an old mouse new clicks.

JoeIngle
Starting Member

14 Posts

Posted - 2004-03-25 : 05:04:26

CREATE TABLE #Temp(ID int)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb.[dbo].[#Temp]') )

BEGIN
PRINT 'YES'
END
ELSE
BEGIN
PRINT 'NO'
END

"He was a wise man who invented beer."

Plato

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-25 : 05:04:29

Greetings fellow South African

Check this out

create table #rb(Branchcode int)

SELECT o.name
FROM tempdb..syscolumns c
LEFT JOIN tempdb..sysobjects o ON o.id = c.id
WHERE o.name LIKE '#%'
AND c.name = 'BranchCode'

oops - 3 seconds too late

Duane.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-25 : 05:22:01

Greetings Back

Let me re-phrase my question (with a bit more detail this time).


Select His.Enterprise_nbr, Max (his.maint_date) as Maint_date
into #vwTemp
from SQL.sms_system.dbo.history_reo as his
where (his.survey_code = 'Agri2002' or His.Survey_code = 'Agri2003')
Group by His.Enterprise_nbr
                           

Select T.enterprise_nbr, T.maint_date, Hi.capture_code
into #vwTempRes as T
from #vwTemp 
Inner join SQL.sms_system.dbo.history_reo as Hi
on t.enterprise_nbr = Hi.enterprise_nbr
where t.maint_date = Hi.maint_date
and (reo_code = '25' or Reo_code = '28')

Select *
from #vwTempRes
order by Enterprise_nbr

If any of the #temp tables (#vwTemp or #vwTempRes) already exist then I want to drop them then run this statement

Can/Should I include an if or Case statement?

You can't teach an old mouse new clicks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-25 : 05:30:41

CREATE TABLE #VWTEMPRES(Blah INT)
CREATE TABLE #VWTEMP(Blah INT)
CREATE TABLE #VIRGIN(Blah INT)

DECLARE @Name VARCHAR(500)

DECLARE a cursor for
SELECT name from tempdb..sysobjects where name like '#VWTEMP%'

open a
FETCH NEXT FROM a INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP TABLE ' + @Name)
FETCH NEXT FROM a INTO @Name
END

CLOSE a
DEALLOCATE a

Duane.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2004-03-25 : 05:34:40

It all makes sense now

Thanks

You can't teach an old mouse new clicks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-25 : 07:40:48

There's also:

IF OBJECT_ID('tempdb..#vwTemp') IS NOT NULL DROP TABLE #vwTemp
IF OBJECT_ID('tempdb..#vwTempRes') IS NOT NULL DROP TABLE #vwTempRes

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-25 : 07:52:29

Thanks for that Rob.

I Learn many things here every day!

Duane.