looping through a record set

[Alibaba2003]

i need to loop through a recordset or a table. Do some calculations on each row and append the results to multiple tables. What is the recommended approach to do this on the server side. I know cursors are slow and the answer should be stored procedures. How do i loop through the recordset.
How do i refer to columns in the record set.

For those of you familiar with ADO, here is what i am trying to do:


do while not recordsetVariable.eof

variable = table1.field3 + field4

table1!field1value = constant + recordsetVariable!field1value

table1!field2value = variable + recordsetVariable!field1value



recordsetVariable.movenext
loop

thanks



It's Nice to Be Important But It's more Important to BE Nice

 

[gmmastros]

Please post several rows of sample data and expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Alibaba2003]

 do while not end of recordsetOfSourceTable

--- insert calculated values into table1
--- insert more calculated values into table2
--- more insertion statements

get the next recordsetOfSourceTable
end do while

It's Nice to Be Important But It's more Important to BE Nice

 

[Nagrom]

Are you actually inserting new rows into a table or updating existing rows? In your example you're adding the values in Field3 and Field4 of Table1, which suggests you're working with an existing row, but later you say you need to "insert calculated values" into Table1.

 

[Alibaba2003]

i have a source table .. i want loop through that one ..
Update some records in some tables.
Add records to a table.
Keep the ID inserted in one of the added tables
Add records to another table using that ID [PK]

so the answer all of the above .. my problem is looping through the recordset.

I need to find out how to get each column value separately from the source table and use it to update or add to a new table.

Thanks

It's Nice to Be Important But It's more Important to BE Nice

 

[Nagrom]

There are two different sets of SQL statement types you can use for each of these that would be faster than looping through each of the records in the recordset individually. They are the "insert into... select..." and "update... from..." statements.

The first type, "insert into... select..." inserts records in a table based on a select query. For example, if you have two tables:
Table1: Field1, Field2, Field3, Field4
Table2: Field1
and you want to store the sum of Fields 3 and 4 from Table1 in Field1 of Table2 you would use this syntax:

insert into Table2 (Field1) 
select Field3 + Field4 
from Table1

That's a simple example, but your "select" could be as complex of a query as you needed to get the job done.

Likewise, if you had two tables:
Table1: IDField, Field1, Field2, Field3
Table2: IDField, Field1
and you wanted to updated the corresponding Table2 record with the sum of Table1's Field1 and some constant, you could do it like this:

update Table2 
set Field1 = DataSubset.NewValue 
from (select IDField, Field1 + (your constant) As NewValue 
      from Table1) As DataSubset 
where Table2.IDField = DataSubset.IDField