如何将auto_increment键插入到SQL Server表中?

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

我希望将行插入具有惟一的、非自动递增主键的表中。

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

是否有一个本地SQL函数来计算最后一个键并对其进行递增，还是我必须用两个步骤来完成:

key = select max(primary.key) + 1

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

4 个解决方案

#1

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

根据您自始至终的评论判断，表上的主键不是标识列。

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

如果您的SQL Server版本是SQL 2012，您应该查看序列:http://msdn.microsoft.com/en-us/library/ff878091.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.

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

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:

如果使用两步方法，您需要确保并发运行的插入不会最终使用相同的新值。最简单的方法是将select和insert合并到一个值中，并使用serializable表提示:

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;

#2

Since it's auto-generated, simply don't provide it:

因为它是自动生成的，所以不要提供:

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

Update: that will work if your column is an IDENTITY column.

更新:如果您的列是标识列，那么它将工作。

To provide explicit values to an identity column, you have to do this:

要为标识列提供显式值，您必须这样做:

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

But there's no compelling reason for doing it this way.

但这样做没有什么令人信服的理由。

#3

In my opinion the best answer is to fix your table so that the PK column is an identity column. (Please see my comments on the answer from Sebastian Meine about why your currently selected answer is not best.) The only way to make an existing PK become an identity column is by swapping out the table. Roughly:

在我看来，最好的答案是修复表，使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;

You now have a table that has an identity column with a clustered PK on it. You can insert to it no problem. No more concurrency issues and silly SELECT Max() + 1 junk that is so easy to get wrong.

现在有一个表，其中有一个标识列，上面有一个集群PK。你可以插入它，没有问题。不再有并发问题和愚蠢的选择Max() + 1垃圾，这很容易出错。

#4

create table if not exists Emp ( eid int(10) not null primary key auto_increment, name varchar(45) not null, age int(5) default 20, salary int(5) ) insert into emp values(102,'Ranjan',21,450000);

创建不存在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);

Then try below sql query . It will automaticaly increment the eid to next number .

然后尝试下面的sql查询。它会自动将eid增加到下一个数字。

insert into emp (name,salary) values( 'Lisma',118500);

插入emp(姓名、工资)值('Lisma'，118500);

select * from emp;

从emp select *;

#1