制作100个随机数，并将它们作为主键添加到表中，如果重复，则捕获错误

Question

我有个问题。我试图添加100个随机主键，如果值重复，则捕获错误，然后添加计数错误数量的计数器，并在最后打印出ID中有多少值以及有多少重复数错误。

到目前为止，我已经做到了这一点，但我真的是T-SQL的新手，我不确定这是否接近。

最大的问题是，我不知道如何从WITH中获取数值，并将给定的数字插入到表中。

DECLARE @TABLE TABLE (ID INT NOT NULL PRIMARY KEY)
DECLARE @I INT = 1
DECLARE @ERROR INT = 0
DECLARE @NUMBER INT
BEGIN TRY
WITH CTE_Numbers(number) AS
(
SELECT 1 AS number
UNION ALL 
SELECT number + 1
FROM CTE_Numbers
WHERE number < 100 
)
SELECT TOP 1 number
FROM CTE_Numbers
ORDER BY NEWID()
OPTION (MAXRECURSION 0)
BEGIN
WHILE @I <= 100
BEGIN   
SET @NUMBER = (SELECT number FROM CTE_Numbers)
INSERT INTO @TABLE VALUES(@NUMBER)
SET @I = @I + 1
END
END
END TRY
BEGIN CATCH
SET @ERROR = @ERROR + 1
END CATCH
SELECT COUNT(ID) AS numbers, @ERROR AS errors
FROM @TABLE

Answer 1

我不完全理解你在做什么，但这应该会让你开始@rows是您想要的行数，@high是最高ID(介于1和@high之间(。

--==== Parameters
DECLARE 
@rows INT = 10, -- Return this many rows
@high INT = 20;  -- Number = 1 to @high
--==== Prep
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
--==== Create and Populate #t with random numbers 
WITH 
e1(x) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
t(N)  AS (SELECT ABS(CHECKSUM(NEWID())%@high)+1 FROM e1 a, e1 b, e1 c, e1 d) -- up to 10K
SELECT TOP(@rows) t.N INTO #t FROM t;
--==== Counts
SELECT
UniqueIds  = SUM(IIF(f.T=1, 1, 0)),
Duplicates = SUM(IIF(f.T=1, 0, 1))
FROM
(
SELECT   t.N, COUNT(*)
FROM     #t AS t
GROUP BY t.N
) AS f(N,T);
--==== Santiy Check
SELECT   t.N, ttl = COUNT(*)
FROM     #t AS t
GROUP BY t.N

返回：(随机(

UniqueIds   Duplicates
----------- -----------
2           4
N           ttl
----------- -----------
2           2
5           1
6           2
8           2
12          2
14          1