如何在 SQL Server 表中插入 auto_increment 键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13994736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert an auto_increment key into SQL Server table
提问by Ruben Teixeira
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)
回答by Jord?o
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 IDENTITYcolumn.
更新：如果您的列是IDENTITY列，这将起作用。
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.
但是这样做没有令人信服的理由。
回答by Sebastian Meine
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:
如果您采用两步法，您需要确保并发运行的插入操作最终不会使用相同的新值。最简单的方法是将选择和插入组合成一个值并使用可序列化表提示：
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;
回答by ErikE
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() + 1junk that is so easy to get wrong.
您现在有一个表，该表有一个标识列，上面有一个聚集的 PK。你可以插入它没问题。没有更多的并发问题和很容易出错的愚蠢的SELECT Max() + 1垃圾。
回答by PyDevSRS
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 主键 auto_increment, name varchar(45) not null, age int(5) default 20,salary int(5) ) insert into emp values(102,'Ranjan' ,21,450000);
Then try below sql query . It will automaticaly increment the eidto next number .
然后尝试下面的 sql 查询。它会自动将eid增加到下一个数字。
insert into emp (name,salary) values( 'Lisma',118500);
插入 emp (name,salary) values('Lisma',118500);
select * from emp;
从 emp 中选择 *；
