Building temp tables conditionally

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

SQL Server Development (2000)

Building temp tables conditionally

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-03-22 : 13:06:04

Hi Im trying create a temp table from a varying source depending on a given value:
declare @t as int
set @t=1
if(@t=1)
begin
select * into #temp from MyTable
end
else
begin
select * into #temp from MyTable2
end

---------
Result:
Server: Msg 2714, Level 16, State 1, Line 11
There is already an object named '#temp' in the database.

If I change the second tsble name to #temp2 it works but this is not what I want, does anybody have a suggestion?

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 14:00:41

create the table first then conditionally insert into.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-23 : 01:36:42

or use different temp table names

Madhivanan

Failing to plan is Planning to fail

heze
Posting Yak Master

192 Posts

Posted - 2006-03-23 : 01:46:29

I opted for mad's option although because the table contains different content according to the condition, therefore in reality I need 2 tables. On the other hand what I pretended was to create a table with the same name conditionally and common operations out of the if statement ie
@var=1
if @var=1
begin
select Content1.* into #t from ...
end
else
begin
Content2.* into #t from ...
end
select * from #t
select avg(SomeField) from #t
-------
the problem is that now the last operations are inside each if statement, thus are replicated and could b a source of eror in the future

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources