SQL Server:插入下一个可用的int [英] SQL Server: insert next available int

问题描述

我正在处理SQL Server中具有serial_no列的表，该表定义为非null int.它似乎不是一个自动递增的字段，就好像我将该列放在我的插入语句中之外时，我收到一条错误消息，指出serial_no字段不能为空.

I'm dealing with a table in SQL Server that has a serial_no column, which is defined as a non null int. It doesn't appear to be an auto incrementing field, as if I leave that column out of my insert statement I get an error saying that the serial_no field cannot be null.

那我该如何插入下一个可用号码?

So how do I insert the next available number?

我尝试过:

INSERT INTO mytable (serial_no) VALUES ( (SELECT MAX(serial_no)+1 FROM mytable))

但是我收到一条错误消息，指出不能在这种情况下使用子查询.

but I get an error saying that subqueries cannot be used in this context.

此表用于现成的产品中，因此我无法更改设计并使serial_no列为自动递增.

This table is used in an off the shelf product so I cannot change the design and make the serial_no column an auto increment.

推荐答案

您可以通过锁定提示来提高写入并发性

You can improve write concurrency with locking hints

INSERT INTO mytable (serial_no, value)
SELECT MAX (serial_no)+1, @value 
FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)

如果性能并不重要，请尝试使用TABLOCKX代替ROWLOCK，XLOCK

If performance is't important, try TABLOCKX in place of ROWLOCK, XLOCK

但是，鉴于此方法不安全，您也需要重试

However, given this isn't safe either you need to retry

DECLARE @retry bit
SET @retry = 1

WHILE @Retry = 1
BEGIN
    BEGIN TRY
        INSERT INTO mytable (serial_no, value)
        SELECT MAX (serial_no)+1, @value 
        FROM mytable WITH (ROWLOCK, XLOCK, HOLDLOCK)

        SET @Retry = 0
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627  --PK violation
            RAISERROR ('blah', 16, 1)
    END CATCH
END

或更改为IDENTITY列并正确执行...

这篇关于SQL Server:插入下一个可用的int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！