如何循环运行100次写到表#test里的记录有600行记录？请各位专家解答

Question

---随机生成一组6个号，包含33个数字，前5个各不相同，运行一次写入6行，运行2次写入12行，如何循环运行100次，把600行写入到 表 #test
 
SET NOCOUNT ON
IF OBJECT_ID(N'ssq2', N'U') IS NOT NULL 
DROP TABLE ssq2;
GO
create table ssq2(num int,grp int)
go

declare @i int
set @i=1
while @i<=33
begin
insert into ssq2 values(@i,0)
set @i=@i+1
end

IF OBJECT_ID(N'ssq3', N'U') IS NOT NULL 
DROP TABLE ssq3;
GO
select num,IDENTITY(int, 1,1) AS ID into ssq3 from ssq2 order by newid();

update a
set grp=(case when b.id<=6 then 1
when b.id<=12 and b.id>6 then 2
when b.id<=18 and b.id>12 then 3
when b.id<=24 and b.id>18 then 4
when b.id<=30 and b.id>24 then 5
when b.id<=33 and b.id>30 then 6 end)
from ssq2 a left join ssq3 b on a.num=b.num

declare @a1 int,@a2 int,@a3 int
select @a1=abs(checksum(newid())) % 33+1
while @a1 in (select num from ssq2 where grp=6)
select @a1=abs(checksum(newid())) % 33+1

insert into ssq2 values(@a1,6)

select @a2=abs(checksum(newid())) % 33+1
while @a2 in (select num from ssq2 where grp=6)
select @a2=abs(checksum(newid())) % 33+1

insert into ssq2 values(@a2,6)

select @a3=abs(checksum(newid())) % 33+1
while @a3 in (select num from ssq2 where grp=6)
select @a3=abs(checksum(newid())) % 33+1

insert into ssq2 values(@a3,6)

DROP TABLE ssq3;
select num,(select count(num) from ssq2 where grp=a.grp and num<=a.num) id,grp into ssq3 from ssq2 a
SET NOCOUNT OFF
;with t as (
select max(case when id=1 then num else 0 end) R1,
max(case when id=2 then num else 0 end) R2,
max(case when id=3 then num else 0 end) R3,
max(case when id=4 then num else 0 end) R4,
max(case when id=5 then num else 0 end) R5,
max(case when id=6 then num else 0 end) R6
from ssq3
group by grp
)
--if object_id('tempdb..#test') is not null drop table #test 
--go
--create table #test(n1 int,n2 int,n3 int,n4 int,n5 int,n6 int)  
 insert into #test    ----问题 运行一次写入6行，如何循环运行100次，把600行写入到 表 #test
 select * from t

 select * from  #test

Answer 1

DROP TABLE IF EXISTS result
DECLARE @loop2 INT=0 
DECLARE @Nums NVARCHAR(100)
DECLARE @sql NVARCHAR(200)=''

CREATE TABLE result (GroupID INT, N1 INT,N2 INT,N3 INT, N4 INT,N5 INT, N6 INT)

DECLARE @GroupID INT=1
WHILE @GroupID < 101
    BEGIN
        SET @loop2 = 0;
        DROP TABLE IF EXISTS #Seq;
        --生成随机排序的1~33序列
        SELECT  TOP (33) [number], NEWID() ID
        INTO    #Seq
        FROM    [master].[dbo].[spt_values]
        WHERE   type = 'p' AND  number < 34 AND number > 0;
        --循环6次，写入6行
        WHILE @loop2 < 6
            BEGIN
                SET @Nums = STUFF((   SELECT    ',' + CAST(number AS VARCHAR(2))
                                      FROM      #Seq
                                      ORDER BY  ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY
                                      FOR XML PATH('')),
                                  1,
                                  1,
                                  '');
                --第6次数字不够，随机找3个补上
                IF @loop2 = 5
                    BEGIN
                        SET @Nums = @Nums + (   SELECT      ',' + CAST(number AS VARCHAR(2))
                                                FROM        #Seq
                                                ORDER BY    NEWID()OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
                                                FOR XML PATH(''));
                    END;
                --生产写入的SQL代码
                SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6) VALUES (' + CAST(@GroupID AS NVARCHAR(3))
                           + N',' + @Nums + N')';
                --执行写入
                EXEC (@sql);
                SET @loop2 = @loop2 + 1;
            END;
        SET @GroupID = @GroupID + 1;
    END;
SELECT * FROM result