在SQL Server中，如何使用串联ID在循环中N次插入表

Question

我想在SQL SERVER中插入一个连接的id，并重复它N次。

事实上，我有这样的东西：

DECLARE @COUNT INT;
DECLARE @NUM_INSERTS INT;
SET @COUNT = 1;
SET @NUM_INSERTS = 10;
WHILE (@COUNT <= @NUM_INSERTS)
BEGIN
DECLARE @randomID VARCHAR(2);  
SET @randomID = CAST(ABS(CHECKSUM(NEWID()) % 10) AS VARCHAR(1)) + CAST(ABS(CHECKSUM(NEWID()) % 10) AS VARCHAR(1)); 
SET @ID = CONCAT(@randomID,@COUNT); 
INSERT INTO Table(ID, colB, colC, colD)
SELECT 
@ID,
colB, 
colC, 
colD
FROM #tempTable
SET @COUNT = @COUNT + 1; 
END

此外，我想生成一个级联的id

？使用递归CTE可以做到这一点吗？

Answer 1

以下是如何使用递归cte&交叉应用：

DECLARE @NUM_INSERTS INT;
SET @NUM_INSERTS = 10;
with cte as (
select 1 num
union all
select num+ 1 as num
from cte 
where num < @NUM_INSERTS
)
INSERT INTO Table(ID, colB, colC, colD)
SELECT CAST(ABS(CHECKSUM(NEWID()) % 10) AS VARCHAR(1)) 
+ CAST(ABS(CHECKSUM(NEWID()) % 10) AS VARCHAR(1)) 
+ cte.num as ID
,colB,colC,colD
FROM #tempTable
cross apply cte;