I want to divide records in table with specific group size in SQL Server-CodePudding

For example, I have one TABLE1 with huge numbers of records, ID is primary key

ID	Col1	COL2
1	1	x
2	2	y
3	2	z
4	3	a
5	4	b
6	5	c
7	5	d

so on....

Now i have empty table TABLE2 with additional column counter

ID	COL1	COL2	counter

I want to fill TABLE2 from TABLE1 with counter set as 1 for first n record and 2 for next n records and so on. 'n' is variable here holding value of group size

Let n = 2 and when we fill TABLE2 then Expected Result

ID	COL1	COL2	counter
1	1	x	1
2	2	y	1
3	2	z	2
4	3	a	2
5	4	b	3
6	5	c	3
7	5	d	4

CodePudding user response：

Just insert the result of this into your new table:

SELECT *
     , ((ROW_NUMBER() OVER (ORDER BY id)-1) / 2)   1 AS n0
  FROM t1
;

CodePudding user response：

INSERT INTO TABLE2(ID,COL1,COL2,counter)
SELECT ID,COL1,COL2,
((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1) / n   1)
FROM TABLE1

CodePudding user response：

Try this:

DECLARE @Table1 TABLE   (ID int, Col1 int, COL2 char(1))
DECLARE @Table2 TABLE   (ID int, Col1 int, COL2 char(1), [Counter] int)
;
DECLARE @N int = 2;

INSERT INTO @Table1
    VALUES  
            (1, 1,  'x')
        ,   (2, 2,  'y')
        ,   (3, 2,  'z')
        ,   (4, 3,  'a')
        ,   (5, 4,  'b')
        ,   (6, 5,  'c')
        ,   (7, 5,  'd')
;
INSERT INTO @Table2
SELECT 
        ID
    ,   Col1
    ,   Col2
    ,   [Counter]   =   ((ID-1) / @N)   1
  FROM @Table1
;

SELECT * FROM @Table2;