Iterate temp table in batches

Question

i have temp table with a set of data

-----------------------------------------------------
| col1  | col2      | col3      | col4      | status| 
-----------------------------------------------------
| a     | a12       | dd        | ff        | 1     | 
-----------------------------------------------------
| b     | b43       | dd        | ff        | 2     | 
-----------------------------------------------------
| c     | fe3       | dd        | ff        | 3     |
-----------------------------------------------------
| d     | fd2       | gg        | hh        | 1     |   
-----------------------------------------------------               
| e     | sf2       | gg        | hh        | 1     |   
-----------------------------------------------------   
| f     | vd2       | ii        | jj        | 3     |
-----------------------------------------------------
| g     | cd3       | ii        | jj        | 3     |
-----------------------------------------------------

I need to process to table in batches to select some of the rows.

i.e

first consider the rows with col3 =dd and col4=ff and select only one row (i have a algorithm to select this row) then consider the rows with col3=gg and col4=hh and select onse then consider the rows with col3=ii and col4=jj and select one row.

How can i iterate through my temp table to select subset of row and process .??

i need to get one row from each subset (subset with same col3 and col4) based on status Column.

Expected Result:-

| col1  | col2      | col3      | col4      | status| 
-----------------------------------------------------
| b     | b43       | dd        | ff        | 2     | 
-----------------------------------------------------    
| d     | fd2       | gg        | hh        | 1     |   
-----------------------------------------------------               
| f     | vd2       | ii        | jj        | 3     |
-----------------------------------------------------

Not clear on intent. A simple where clause in your query will allow you to retrieve a subset of the data in the table above, however, I am not sure that is your goal. — Ross Bush, Nov 17 '16 at 15:31
This question lacks context. It seems odd to make multiple trips to a database when you could simply order the recordset by your algorithm and you could parse the result set at the User Interface or in backoffice logic. — xQbert, Nov 17 '16 at 15:33
i am not sure about the data in col3 and col4 And No.of records in my temp table. instead of multiple hits to DB, Collected all data in to a temp table and trying to process temp table. — Sahi, Nov 17 '16 at 15:37
in general looping through records is considered a "bad idea". Try to perform your logic in a "set based" way. with no other options, take a look at this question. http://stackoverflow.com/questions/20662356/sql-server-loop-how-do-i-loop-through-a-set-of-records — Jeremy, Nov 17 '16 at 15:48
@Sahi Can you provide the algorithm to select the row where certain conditions apply in words? It's always best to have a set-based approach rather than iterate the table using a cursor or a while loop. — TT., Nov 18 '16 at 04:55

score 0 · Answer 1 · answered Nov 17 '16 at 17:15

It depends on the purpose and database engine.

If you want iterate and modify data in this table you can use cursor, while loop, cte, recursive update...
If you want iterate objects (tables, databases) you should use sytem foreach based procedure (for example sp_MSforeachdb).
If you want get some value and only select something from next table you should use function.
If you want iterative solution you can use cursor or while loop. But try rethink your plan and try use set-based solution (of course, if it's possible).

score 0 · Answer 2 · answered Nov 18 '16 at 09:59

You can achieve this with windowed functions, partitioning on whatever set of columns you want a distinct row for:

declare @t table (ID int, TextValue nvarchar(10));
insert into @t values(1,'a'),(2,'a'),(3,'a'),(4,'b'),(5,'b'),(6,'b'),(7,'c'),(8,'c'),(9,'c');

select ID
      ,TextValue
from(select ID
            ,TextValue
            ,row_number() over (partition by TextValue order by ID) as rn
    from @t
) a
where rn = 1;

Iterate temp table in batches

2 Answers2