只需简单的 SQL INSERT 就可以实现手动增量吗? [英] Possible to implement a manual increment with just simple SQL INSERT?

问题描述

我有一个我不想自动递增的主键(出于各种原因)，因此我正在寻找一种方法来在我插入时简单地递增该字段.简单来说，我的意思是没有存储过程和触发器，所以只有一系列 SQL 命令(最好是一个命令).

I have a primary key that I don't want to auto increment (for various reasons) and so I'm looking for a way to simply increment that field when I INSERT. By simply, I mean without stored procedures and without triggers, so just a series of SQL commands (preferably one command).

这是我迄今为止尝试过的:

Here is what I have tried thus far:

BEGIN TRAN

INSERT INTO Table1(id, data_field)
VALUES ( (SELECT (MAX(id) + 1) FROM Table1), '[blob of data]');

COMMIT TRAN;

* Data abstracted to use generic names and identifiers

但是，当执行时，命令出错，说

However, when executed, the command errors, saying that

"这里不允许子查询语境.只有标量表达式是允许"

"Subqueries are not allowed in this context. only scalar expressions are allowed"

那么，我该怎么做/我做错了什么?

So, how can I do this/what am I doing wrong?

因为它被指出为一个考虑因素，所以要插入的表保证已经至少有 1 行.

Since it was pointed out as a consideration, the table to be inserted into is guaranteed to have at least 1 row already.

推荐答案

你明白会发生碰撞吧?

你需要做这样的事情，这可能会导致死锁，所以要非常确定你要在这里完成什么

you need to do something like this and this might cause deadlocks so be very sure what you are trying to accomplish here

DECLARE @id int
BEGIN TRAN

    SELECT @id = MAX(id) + 1 FROM Table1 WITH (UPDLOCK, HOLDLOCK)
    INSERT INTO Table1(id, data_field)
    VALUES (@id ,'[blob of data]')
COMMIT TRAN

为了解释碰撞的事情，我提供了一些代码

To explain the collision thing, I have provided some code

先创建这个表并插入一行

first create this table and insert one row

CREATE TABLE Table1(id int primary key not null, data_field char(100))
GO
Insert Table1 values(1,'[blob of data]')
Go

现在打开两个查询窗口并同​​时运行它

Now open up two query windows and run this at the same time

declare @i int
set @i =1
while @i < 10000
begin
BEGIN TRAN

INSERT INTO Table1(id, data_field)
SELECT MAX(id) + 1, '[blob of data]' FROM Table1

COMMIT TRAN;
set @i =@i + 1
end

你会看到一堆这样的

服务器:消息 2627，级别 14，状态 1，第 7 行违反主键约束PK__Table1__3213E83F2962141D".无法在对象dbo.Table1"中插入重复键.声明已终止.

Server: Msg 2627, Level 14, State 1, Line 7 Violation of PRIMARY KEY constraint 'PK__Table1__3213E83F2962141D'. Cannot insert duplicate key in object 'dbo.Table1'. The statement has been terminated.

这篇关于只需简单的 SQL INSERT 就可以实现手动增量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！