只需简单的 SQL INSERT 就可以实现手动增量吗?

本文介绍了只需简单的 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

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

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

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 7Violation of PRIMARY KEY constraint 'PK__Table1__3213E83F2962141D'. Cannot insert duplicate key in object 'dbo.Table1'.The statement has been terminated.

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