How to assign GUID to batches of data based on row counts and tunable batch size

Question

I need to assign batches of rows a common GUID id (for consumption by an external process).

Below is a simplified setup describing what I'm looking for:

BASE_TABLE represents one of pre-existing tables in this scenario, much simplified obviously. Values in the "real" table should be considered random; they're not consecutive in real life (even the ID is not really an int)

CREATE TABLE BASE_TABLE (
    ID int
    ,VALUE1 varchar(10)
    ,VALUE2 varchar(255)
) 

TARGET_TABLE represents the "output" of the process I'm working on, which is just the input to something else that requires batches of data to have a uniqueIdentifier BATCHID for each batch. Batches in real life are ~1000 rows each.

CREATE TABLE TARGET_TABLE 
(
  ID int
  ,VALUE1 varchar(10)
  ,VALUE2 varchar(255)
  ,BATCHID uniqueIdentifier
)

(Add some dummy data)

DECLARE @DATA1 int
set @DATA1=0
WHILE @DATA1<100
BEGIN
  INSERT INTO BASE_TABLE (ID,VALUE1,VALUE2) VALUES (@DATA1,'v1'+CONVERT(varchar,@DATA1), 'v2'+CONVERT(varchar,@DATA1))
  SET @DATA1=@DATA1+1
END

/** example DESIRED RESULT, with batch size of 5

ID	VALUE1	VALUE2	BATCHID
0	v10	v20	38B1B4FB-7F1E-44FD-9336-19095C01C629
1	v11	v21	38B1B4FB-7F1E-44FD-9336-19095C01C629
2	v12	v22	38B1B4FB-7F1E-44FD-9336-19095C01C629
3	v13	v23	38B1B4FB-7F1E-44FD-9336-19095C01C629
4	v14	v24	38B1B4FB-7F1E-44FD-9336-19095C01C629
5	v15	v25	41122454-A743-4545-8F0C-D7B461E072AE
6	v16	v26	41122454-A743-4545-8F0C-D7B461E072AE
7	v17	v27	41122454-A743-4545-8F0C-D7B461E072AE
8	v18	v28	41122454-A743-4545-8F0C-D7B461E072AE
9	v19	v29	41122454-A743-4545-8F0C-D7B461E072AE
10	v110	v210	41122454-A743-4545-8F0C-D7B461E072AE
11	v111	v211	FBDE5513-C869-4F2D-AC4D-40CBEF4A2D48

etc

Important result is that for each batch of size N there are N rows with the same BatchID, which must be a GUID.

I'm pretty sure I could do what I want with a cursor, but I want to do something like this if possible:

select 
ID,VALUE1,VALUE2,
NEWID() OVER(PARTITION BY ROW_NUMBER() OVER (ORDER BY ID)) AS BatchID
from BASE_TABLE

but it's not valid because NEWID() isn't an aggregate function.

Answer 1

you have to play with row_numbers

 declare @part int = 3
 
 ;with baseRowNums as 
  (
    SELECT ID, VALUE1, VALUE2,
       Row_Number() Over (ORDER BY ID) AS rn
    FROM BASE_TABLE
  )
 ,batchNums as
 (
 SELECT *,
    Sum(CASE WHEN rn % @part = 1 THEN 1 ELSE 0 END)
    Over (ORDER BY ID, rn 
          ROWS Unbounded Preceding) AS part
 FROM baseRowNums
 )
 ,GUIDs as
 (select part, MAX(newid()) as GUID
  from batchNums
  group by part
 )
 select ID, VALUE1, VALUE2, GUIDs.GUID
 from batchNums
 join GUIDs on batchNums.part = GUIDs.part

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7c26d9bab095ce9d8ca1abf1f4c1a898