Error:There is already an object

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

General SQL Server Forums

New to SQL Server Programming

Error:There is already an object

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-02-22 : 09:02:59

declare @pre_franchises table
(
class char(20)
)
insert into @pre_franchises
select 'blimp' union all
select 'subway' union all
select 'quizno' union all
select 'ponderosa' union all
select 'quincy'
select * into #franchises
from @pre_franchises

If I add one more string into @pre_franchise and rerun this piece of code I got error

erver: Msg 2714, Level 16, State 6, Line 11
There is already an object named '#franchises' in the database.

How can I solve this problem? (I only have select right to the database)
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-22 : 09:07:55

The table #Franchises and @Franchises are not the same table.
You have to DROP the table #Franchises first.

This is not common in production stored procedures, this mostly happens in Query Analyzer and SSMS when running the code twice or more.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 09:09:00

select * into #franchises
from @pre_franchises

you need to

DROP TABLE #franchises

before you can do this a second time.

Or you should

INSERT into #franchises
select *
from @pre_franchises

for subsequent uses

(You may want to

DELETE #franchises

first to "empty" the Temp table

NOTE that it would be better to CREATE #franchises before using it, rather than using "SELECT ... INTO #franchises" because that will potentially hold a lock on TEMPDB for extended periods.

Kristen

jeff06
Posting Yak Master

166 Posts

Posted - 2007-02-22 : 09:19:09

Thank you very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources