我收到一条错误消息，但它执行正常？

Question

IF EXISTS (SELECT name
           FROM sys.tables
           WHERE name = 'Nums')
BEGIN
    DROP TABLE dbo.Nums;
END
CREATE TABLE dbo.Nums
(
     number INT NOT NULL, 
     CONSTRAINT PK_Nums PRIMARY KEY CLUSTERED(number ASC),
     code Char(9),
     date DATETIME
) ON [PRIMARY] 
    
INSERT INTO Nums (number, code, date)
VALUES (0, 485658235, '2000/01/01')
DECLARE @number int, @code Char(9), @date datetime
SET @number = (SELECT MAX (Number) FROM nums)
SET @date = (SELECT Date FROM Nums)
SET @code = (SELECT code FROM Nums)
WHILE @number < 100000
BEGIN
    INSERT INTO Nums--(number, code, date)
    VALUES (@number, @code, @date)
    SET @number = @number + 1
    SET @date = DATEADD(DAY, 5, @date)
    SET @code = LEFT(CAST(CAST(CEILING(RAND()* 10000000000) AS bigint) AS varchar),9) 
END
SELECT * FROM Nums

这是我得到的错误:

Msg 2627, Level 14, State 1, Line 41
违反PRIMARY KEY约束'PK_Nums'。无法在对象'dbo.Nums'中插入重复键。重复键值为(0)

Answer 1

需要在循环开始前给数字加1。

错误Violation of PRIMARY KEY constraint通常不是批量中止，因此执行将继续执行下一条语句。

如果你想停止它，要么强制批处理中止并回滚:SET XACT_ABORT ON(可能是个好主意)，或者使用TRY/CATCH。

我假设您也意识到您的过程可以在一个语句中完成，通过连接一个计数表或函数(例如Itzik Ben-Gan的这些)，像这样:

INSERT @Nums (number, code, date)
SELECT
    t.Num,
    LEFT(CAST(CAST(CEILING(RAND()* 10000000000) AS bigint) AS varchar),9),
    '2000/01/01'
FROM fnTallyTable (0, 99999) t