Variables and temp tables in While loops

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)

Variables and temp tables in While loops

Author

Topic

etietje
Starting Member

24 Posts

Posted - 2003-01-16 : 10:50:20

Hello. Before you freak at the amount of typing I've done, there is an example of the code at the bottom. Thank you in advance :)

I am having trouble with a query I am running. I have to get store data from multiple tables. In essence, I am using two While loops to cycle through data. The first While loop reads data from multiple archive tables and loads a temp table. The second While loop reads data from another database and loads the shared temp table. The problem I'm running into is that I'm using other temp tables with the same name in each loop. Each loop is essentially the same query with slight modifications to allow for the different naming convention for our archive tables. Inside each loop is an IF statement checking the value of a variable that gets set at the end of each loop. When the loop starts, the variable is 0, and the IF statement runs a Select into #temp where 1=0. Once that has been run, the variable is set to 1, and the code in the IF statement shouldn't be run again. Now for my problem :) I keep getting errors when I run both loops in the same query. When I run each of them separately, it runs fine. The errors tell me that the temp tables I am using in the IF statement are already in the database.

There is an example of my query below. Any help would be greatly appreciated.

declare @runbefore as integer

--This loop may run if @counter is small enough, but many times may not
while @counter < @currentweeknumber
begin

select * into #temp1
from sometable

IF @runbefore = 0
begin
select *
into #temp2
from #temp1
where 1=0
and other stuff
end

insert #temp2
select *
from #temp1
where something = somethingelse

set @runbefore = 1
drop table #temp1
end

--This is the loop where I am having the problem
--The error says that object named #temp1 and #temp2 already exists in the database
while @counter2 < @currentweeknumber
begin

select * into #temp1
from sometable

IF @runbefore = 0
begin
select *
into #temp2
from #temp1
where 1=0
end

insert #temp2
select *
from #temp1
where something = somethingelse

set @runbefore = 1
drop table #temp1
end

select * from #temp2

drop table #temp2

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-16 : 10:57:06

Well, the table does already exist, so you will get that error. You could try changing the select..into in your second loop into an insert .. into select from..

Though to be honest I'm not sure why exactly you're doing it this way with all the while loops it seems very convoluted.

-------
Moo.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2003-01-16 : 11:02:32

First of all I don't see a point in using while loops. Your inserts don't need them.
I think you should rethink the whole batch again...

If you stick to it though, try using DECLARE @T1 TABLE instead of CREATE #T.

etietje
Starting Member

24 Posts

Posted - 2003-01-16 : 11:03:13

It is pretty convoluted. I was just told I needed to use these archive tables for past data, and figured I could use the same process I was using on the main table to process the archive data. The code is pretty ugly, but then again, so is the database schema :)

etietje
Starting Member

24 Posts

Posted - 2003-01-16 : 11:05:36

quote:

First of all I don't see a point in using while loops. Your inserts don't need them.
I think you should rethink the whole batch again...

If you stick to it though, try using DECLARE @T1 TABLE instead of CREATE #T.

Doh! I forgot to put in the SET @counter = @counter + 1 and SET @counter2 = @counter2 + 1 at the end of the loops.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources