如何在 SQL Server 表中插入 auto_increment 键 [英] How to insert an auto_increment key into SQL Server table

问题描述

我想将行插入到具有唯一的非自动递增主键的表中.

I want to insert rows into a table that has a unique, non auto-incremented primary key.

是否有本机 SQL 函数来评估最后一个键并增加它，或者我必须分两步完成:

Is there a native SQL function to evaluate the last key and increment it or do I have to do it in two steps:

key = select max(primary.key) + 1

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

推荐答案

从你整篇评论来看，你在表上有一个不是标识列的主键.

Judging by you comments throughout, you have a primary key on the table that is not an identity column.

如果您的 SQL Server 版本是 SQL 2012，您应该查看序列:http://msdn.microsoft.com/en-us/library/ff878091.aspx

If your version of SQL Server is SQL 2012 you should look into sequences: http://msdn.microsoft.com/en-us/library/ff878091.aspx

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

In other versions you either need to recreate the table using the IDENTITY property (http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx) for the primary key column or use a two step approach.

如果您采用两步法，您需要确保并发运行的插入操作最终不会使用相同的新值.最简单的方法是将选择和插入组合成一个值并使用可序列化表提示:

If you go with the two step approach you need to make sure that concurrently running inserts won't end up using the same new value. The easiest way to do that is this is by combining the select and the insert into one value and using the serializable table hint:

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;

这篇关于如何在 SQL Server 表中插入 auto_increment 键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！