Retrieve One Row at a time

[Chris]

Hi,

I am going to be difficult here... How do I retrieve one row at a
time from a table without using a cursor?

For example, I have a table with 100 rows. I want to retrieve the
data from row 1, do some stuff to it and send it on to anther table,
then I want to grab
row 2, do some stuff to it and send it to another table.

Here is how I am envisioning it:

WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
rowcount)
BEGIN
SELECT * FROM [TABLE] BUT ONLY ONE ROW
.... MANIPULATE THE DATA
INSERT into another table
END

Other notes, I am using SQL Sever 2000....
Thanks and in advance and as always the help is greatly appreciated.

Regards,

CLR

[Shervin]

Re: Retrieve One Row at a time

I don't know why you don't want to use a cursor which is probably the most
suitable means to solve your problem. But anyway, you have some other
options like these:

1. Add a flag to your table. After proccessing each record set the flag and
select the next nonprocessed record (using select top 1).

2. Copy all the records you want into a temporary table and again using
selectp top 1 read them one by one and delete them after processing.

3. Use a temporary table as a list of processed records, after processing
each record add its key to this list and select next record where its key
does not belong to this list.

If you give us more information about what you are exactly looking for and
what your problem is, you'll have a better chance to get the solution.

Shervin




"Chris" <chris@dagran.c om> wrote in message
news:736fadb1.0 309301643.572f3 730@posting.goo gle.com...[color=blue]
> Hi,
>
> I am going to be difficult here... How do I retrieve one row at a
> time from a table without using a cursor?
>
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
>
> Here is how I am envisioning it:
>
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> .... MANIPULATE THE DATA
> INSERT into another table
> END
>
> Other notes, I am using SQL Sever 2000....
> Thanks and in advance and as always the help is greatly appreciated.
>
> Regards,
>
> CLR[/color]

[Erland Sommarskog]

Re: Retrieve One Row at a time

Chris (chris@dagran.c om) writes:[color=blue]
>
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
>
> Here is how I am envisioning it:
>
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> .... MANIPULATE THE DATA
> INSERT into another table
> END
>
> Other notes, I am using SQL Sever 2000....
> Thanks and in advance and as always the help is greatly appreciated.[/color]

SELECT TOP 1 @key = keycol, @var1 = col1, @var2 = col2'
FROM tbl
WHERE keycol > @key
ORDER BY keycol

If you have a multi-column, you can still do this, but logic becomes
hairier.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chris]

Re: Retrieve One Row at a time

I want to thank you both for your answers they have helped
tremendously. We are going to use a cursor for our problem as well, I
just wanted another way of handling what we are triyng to accomplish.
We have a table with over a million rows, which from one row we will
query about 5 other tables to extract more information which will be
sent to a new table, then we grab the next row and so on and so forth.
We want to try using a cursor and anther method to see which way
would be more CPU friendly. I feel it doesn't really matter which way
we go, they both will take over my computer. Thanks though for your
responses, it has helped us out a lot!

Regards,

CLR

[Erland Sommarskog]

Re: Retrieve One Row at a time

Chris (chris@dagran.c om) writes:[color=blue]
> I want to thank you both for your answers they have helped
> tremendously. We are going to use a cursor for our problem as well, I
> just wanted another way of handling what we are triyng to accomplish.
> We have a table with over a million rows, which from one row we will
> query about 5 other tables to extract more information which will be
> sent to a new table, then we grab the next row and so on and so forth.[/color]

A million rows iteratively? That could take a couple of days! Sometimes
this can be justified, if it's sort of a one time operation. (Actually,
I was recently involved in writing a task that took 3½ days to complete.)

But if you can find a set-based operation, you can win lots of
performance.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp