How to assign GUID to batches of data based on row counts and tunable batch size 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 &quot;real&quot; 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 &quot;output&quot; 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&lt;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.
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&amp;fiddle=7c26d9bab095ce9d8ca1abf1f4c1a898