只需简单的 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.
相关文章