CODEWITHSUNDEEP
sqlsql-server
Girish Mulgund
Girish Mulgund

Reputation: 75

Select records for batch processing in loop

I need to select the records in batch wise, like in below example we have 20 records. if I give batch of size of 10 there would be two loops. the problem here is if I do top 10 then 555 value will be split as its position is 10 and 11. hence 555 should also include in that top first batch. how I can achieve this? this is just example, I have 900 million records to process and my batch will be 2 million in real scenario. 

ID
-------
111
111
111
222
222
333
333
444
444
555
555
666
666
777
777
888
888

Upvotes: 1

Views: 1202

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82484

You can use top with ties - this might return more records then stated but will not break similar ids to different batches:

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(ID int)

INSERT INTO @T VALUES
(111),(111),(111),
(222),(222),
(333),(333),
(444),(444),
(555),(555),
(666),(666),
(777),(777),
(888),(888)

The select statement: 

SELECT TOP 10 WITH TIES ID
FROM @T 
ORDER BY ID

Results:

row     ID
1       111
2       111
3       111
4       222
5       222
6       333
7       333
8       444
9       444
10      555
11      555

Upvotes: 2

arboreal84
arboreal84

Reputation: 2154

While selecting the records, you can group them by id prior to limiting their number.

Upvotes: 0

Related Questions