Looping in T-SQL

[Chris Reeder]

I want to loop through a recordset and do inserts into another table based on each record.

The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.

I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder

[Paul A]

If you are doing a large record set it could be why it is taking long one record at a time. Also if there are no missing sequential numbers in the temp table then Why not just get the max number one time and decrement the number by one everytime through the loop.

i.e.

DECLARE @maxid int

SELECT @maxid = MAX(id) FROM #blah

WHILE (@maxid) > 0
BEGIN
INSERT INTO blah
(a)
SELECT a from other table where id = @maxid
SET @maxid = @maxid - 1
END





------------
Chris Reeder at 3/19/2002 9:00:18 AM

I want to loop through a recordset and do inserts into another table based on each record.

The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.

I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder

[Chris Reeder]

Thanks Paul. This is one of those "Why didn't I think of that?" ideas. Another related thing I would like to accomplish is to do something for each item in a table. Like in vb: I would open my recordset, be setting on the first record, do something, do a movenext and then be on the next record, then do something with it, etc... until the end of the record set.

Thanks in advance,
Chris Reeder


------------
Paul A at 3/21/2002 1:39:41 PM


If you are doing a large record set it could be why it is taking long one record at a time. Also if there are no missing sequential numbers in the temp table then Why not just get the max number one time and decrement the number by one everytime through the loop.

i.e.

DECLARE @maxid int

SELECT @maxid = MAX(id) FROM #blah

WHILE (@maxid) > 0
BEGIN
INSERT INTO blah
(a)
SELECT a from other table where id = @maxid
SET @maxid = @maxid - 1
END

------------
Chris Reeder at 3/19/2002 9:00:18 AM

I want to loop through a recordset and do inserts into another table based on each record.

The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.

I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder