如何在SQL Server表中插入自增键

Question

我想向一个拥有唯一且非自动递增主键的表中插入行。

是否有原生的SQL函数来评估最后一个键并递增它，或者我必须分两步完成：

key = select max(primary.key) + 1

INSERT INTO dbo.TABLE (primary.key, field1, fiels2) VALUES (KEY, value1, value2)

- Ruben Teixeira

---

你的意思是你在表格中有一个 IDENTITY 列吗？如果是这样，你遇到了什么精确的问题，而这些问题在文档或此网站上关于使用它们的许多问题中都没有讨论到？在下面的评论中，你提到了一个错误，但你并没有说清楚它是什么。最好请提供你的 CREATE TABLE 脚本、INSERT 语句和出现的错误消息。 - Pondlife

这个线程解决了我所有的烦恼，代码也重新编译成功了。为什么有人会给它点踩呢？ - sandun dhammika

Answer 1

由于它是自动生成的，所以只需不提供它即可：

INSERT INTO bo.TABLE (field1, fiels2) VALUES (value1, value2)

更新：如果您的列是IDENTITY列，则这将起作用。

要为标识列提供显式值，您需要执行以下操作：

set identity_insert bo.TABLE on

INSERT INTO bo.TABLE (primary_key, field1, fiels2) VALUES ((SELECT ISNULL(MAX(id) + 1, 0) FROM bo.Table), value1, value2)

set identity_insert bo.TABLE off

但是没有强制的理由用这种方式来做。

- Jordão

---

我已经尝试过了，它会抛出约束错误，我必须提供值。 - Ruben Teixeira

@RubenTeixeira 当你没有指定ID时，你会得到这个错误吗？你确定它没有一个标识列吗？ - Michael Fredrickson

数据库有另外两个主键，即文档系列号和年份。当我没有提供文档编号的键时，它会抛出此错误。 - Ruben Teixeira

看起来你的自增字段不是主键的一部分。你能发布表定义和插入语句吗？ - Jordão

这是一个ERP数据库，它有触发器、存储过程等等。当我提供唯一的键时，它可以正常工作，我必须提供它。 - Ruben Teixeira

Answer 2

根据您在评论中的发言，您的表上有一个非标识列的主键。

如果您使用的是SQL Server 2012版本，则应该研究一下序列：http://msdn.microsoft.com/en-us/library/ff878091.aspx

在其他版本中，您需要重新创建带有IDENTITY属性（http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx）的主键列或使用两个步骤的方法。

如果您选择使用两个步骤的方法，请确保同时运行的插入操作不会使用相同的新值。最简单的方法是将选择和插入组合成一个值，并使用可串行化表提示。

CREATE TABLE dbo.Tbl1(id INT PRIMARY KEY, val1 INT, val2 INT)

INSERT INTO dbo.Tbl1(id, val1, val2)
VALUES((SELECT ISNULL(MAX(id)+1,0) FROM dbo.Tbl1 WITH(SERIALIZABLE, UPDLOCK)), 42, 47);

SELECT * FROM dbo.Tbl1;

- Sebastian Meine

---

谢谢！现在有一个解决方案了，虽然不是一个函数，但我可以用子查询来处理，比两步骤的过程更好。谢谢。 - Ruben Teixeira

SERIALIZABLE相当于HOLDLOCK，是一个不错的开始，但还不够。在高并发情况下，您的查询仍然可能失败。您必须添加UPDLOCK, TABLOCKX。查询的各个部分会随着时间而执行，并且锁也会随着时间而获取——因此，在SELECT之后才会进行INSERT。两个客户端可以同时进行SELECT，具有完全兼容的读取锁，然后第二个客户端被阻塞，而第一个客户端将其转换为UPDLOCK以进行其INSERT，然后第二个客户端在块结束时具有旧值。您需要使SELECT阻止所有读取器！ - ErikE

最终，SELECT max() + 1 模式并不是最佳实践，也不能被推荐。恰好存在自增列来解决这个问题。请使用自增列。 - ErikE

@ErikE，你是对的，我忘记了UPDLOCK。我已经更新了答案。- 但是，不需要TABLOCKX。我们只需要阻止相同的查询读取，而不是任何查询。然后，我们只需要确保任何插入都使用此模式。不遵循此模式的并发插入将被SERIALIZABLE查询提示阻止，并最终失败。- 虽然这不是最好的解决方案，但它是可用的。有时，IDENTITY列只是不可选项。 - Sebastian Meine

如果同步过程一次只插入一条记录，您可以使用相同的模式。如果您需要能够在单个插入语句中插入多行，则仍然可以使用此模式，如果将其与ROW_NUMBER()函数结合使用。重要的是，SELECT max() +1 在所有(!)插入到此表中都使用SERIALIZABLE和UPDLOCK。如果该select不是我的示例中的子选择，则还需要将其与插入一起包装在事务中（如果这发生在过程中，请先阅读此内容：http://sqlity.net/en/585/how-to-rollback-in-procedures/）。 - Sebastian Meine

Answer 3

在我看来，最好的解决方法是将您的表格修正为PK列是自增列。(请参考Sebastian Meine所提供的答案评论，了解为什么您当前选择的答案不是最佳选择。) 将现有的PK变为自增列的唯一方法是通过替换表格。大致流程如下：

BEGIN TRAN;
-- Rename all constraints in original table
EXEC sp_rename 'dbo.YourOriginalTable.PK_ConstraintName', 'PKConstraint_Backup';
EXEC sp_rename 'dbo.YourOriginalTable.OtherConstraintName', 'OtherConstraintName_Backup';
CREATE TABLE dbo.WorkTable (
    YourPKColumn int identity(1, 1) NOT NULL -- your PK converted to identity
    CONSTRAINT PK_YourOriginalTableConstraintName PRIMARY KEY CLUSTERED,
    AllOtherColumns -- all your other columns exactly as in the original table
);

SET IDENTITY_INSERT dbo.WorkTable ON;
INSERT dbo.WorkTable (YourPKColumn, AllOtherColumns)
SELECT YourPKColumn, AllOtherColumns
FROM dbo.YourOriginalTable WITH (TABLOCKX, HOLDLOCK);

SET IDENTITY_INSERT dbo.WorkTable OFF;

-- Drop all FK constraints from other tables pointing to your table
ALTER TABLE dbo.TableWithFK_1
DROP CONSTRAINT FK_TableWithFK_1_YourOriginalTableSomethingID;

-- Swap out the tables
EXEC sp_rename 'dbo.YourOriginalTable', 'YourOriginalTableBackup';
EXEC sp_rename 'dbo.WorkTable', 'YourOriginalTable';

-- If you didn't add them in the WorkTable creation,
-- add all other removed or needed constraints creation
ALTER TABLE dbo.YourOriginalTable
ADD CONSTRAINT OriginalConstraint (OriginalConstraintColumns);
-- Add back FK constraints from other tables to this one.
COMMIT TRAN;

现在你有一个带有聚集主键的标识列的表。您可以轻松地向其插入数据，不再出现并发问题和愚蠢的SELECT Max() +1代码，这种写法非常容易出错。

- ErikE

---

+1 当然，如果这是一个活动系统，您将希望在显式事务中执行此操作。否则，在进行重命名之前插入一些数据到 YourOriginalTable 中存在风险。 - Aaron Bertrand

抱歉，这不是我的数据库，我无法进行任何更改。它是设计用于与ERP应用程序一起使用的。只需要按照问题中所述的过程进行操作即可，不管怎样还是非常感谢您。 - Ruben Teixeira

Answer 4

插入一条记录：

我只是想将一行数据插入到一个非自增主键的表中：

DECLARE @myKey int
SET @myKey = (SELECT  ISNULL(MAX(primaryKey),0) FROM dbo.myTable)

INSERT INTO dbo.myTABLE (primaryKey, field1, fiels2) VALUES (@myKey + 1, value1, value2)

插入多个记录：

在另一种情况下，我需要在表中复制多个记录。为此，我使用了从SELECT结果中的INSERT。 我的意图是复制来自myTable的某些符合特定条件的记录。 但是我需要想出如何在具有唯一主键的表中复制记录，而不重复ID。 以下是我解决问题的方法：

DECLARE @myKey int 
SET @myKey = (SELECT  ISNULL(MAX(primaryKey),0) FROM dbo.myTable)

INSERT INTO dbo.myTABLE (primaryKey, field1, fiels2) 
SELECT 
     (primaryKey + @myKey, value1, value2) 
        FROM dbo.myTable 
        WHERE value2 = especific_Condition

虽然ID存在一些空缺，但我强制保证PRIMARY KEY的唯一性。

- Aprendiz

Answer 5

创建表Emp，如果不存在的话： ( eid int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(45) NOT NULL, age int(5) DEFAULT 20, salary int(5) ) 插入到emp表中值为(102,'Ranjan',21,450000)的数据。

然后尝试以下SQL查询语句，它将自动将eid增加到下一个数字。

插入到emp表中(名字、薪水)的值为('Lisma',118500)的数据。

从emp表中选择所有行。

- PyDevSRS