0

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.

4
  • You want a new guod every 5,6,7,8 rows?
    – nbk
    Mar 2, 2021 at 18:26
  • essentially, yes, but the solution needs to work for "new guid every 1000 rows" too -- basically I want to be able to chunk the data into batches (all the same size) but have the ability to tune the batch size
    – Gus
    Mar 2, 2021 at 18:28
  • what about a stored procedure? with a loop a a runninf varoable to modolo by
    – nbk
    Mar 2, 2021 at 18:30
  • I think that would work, but the input table is large, so I'm trying to avoid cursor-style looping if I can. (might not be possible, and if not, that'll be what I do)
    – Gus
    Mar 2, 2021 at 18:34

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.