可以仅通过简单的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

您会看到很多这样的东西

You will see a bunch of these

服务器:消息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屋！