SQL Server – How to Create Batches of 2000 Records

querysql serverwindow functions

I have a table with 200,000 records.
One of the columns is a location ID.
I need to break down the entire table in batches of 2000 but group them by the LocationID column.

I have about 200 locations with 600 records to 4100 records in each location.

I need to show batch numbers within each location. So if location 001 has 600 records, then there is a batchID of 001-1 which contains 600 records. If location 002 has 2800 records then there are batch IDs 002-1 (2000 records) and 002-2 (800 records). And so on. So a location, 010 with 4100 records would have batchIDs of 010-1,010-2,010-3,010-4,010-5.

I have the following code that works to create batches of 2000, but I can't figure out how to group it by the location #.

SELECT 
 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber, 
(CASE WHEN CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) % 2000)) = 0 
 THEN 0 
 ELSE 1 END) + 
 CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 2000)) AS BatchNumber, 
 * 
FROM [dbo].[LocationRecords]

Best Answer

As mustaccio pointed out you must set a partition in the ROW_NUMBER() function, just to start the counter again for each LocationId.

I've set up the next example:

create table locations (id int identity primary key, location_id varchar(10));

insert into locations values
('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),
('001'),
('002'),('002'),('002'),('002'),('002'),('002'),('002'),('002'),
('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),('003'),
('003'),('003'),('003'),('003'),('003'),('003');

I've used batches of 10 instead of 2000 for the sake of the answer:

SELECT
    id,
    location_id,
    CONCAT (location_id, '-',
            CAST(ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY id) / 10 as VARCHAR(10))
           ) as partition
FROM
    locations;

This is the result:

id | location_id | partition
-: | :---------- | :--------
 1 | 001         | 001-0    
 2 | 001         | 001-0    
 3 | 001         | 001-0    
 4 | 001         | 001-0    
 5 | 001         | 001-0    
 6 | 001         | 001-0    
 7 | 001         | 001-0    
 8 | 001         | 001-0    
 9 | 001         | 001-0    
10 | 001         | 001-1    
11 | 001         | 001-1    
12 | 002         | 002-0    
13 | 002         | 002-0    
14 | 002         | 002-0    
15 | 002         | 002-0    
16 | 002         | 002-0    
17 | 002         | 002-0    
18 | 002         | 002-0    
19 | 002         | 002-0    
20 | 003         | 003-0    
21 | 003         | 003-0    
22 | 003         | 003-0    
23 | 003         | 003-0    
24 | 003         | 003-0    
25 | 003         | 003-0    
26 | 003         | 003-0    
27 | 003         | 003-0    
28 | 003         | 003-0    
29 | 003         | 003-1    
30 | 003         | 003-1    
31 | 003         | 003-1    
32 | 003         | 003-1    
33 | 003         | 003-1    
34 | 003         | 003-1    
35 | 003         | 003-1

db<>fiddle here

Related Question