SQL-如何在不自动增加 ID 列的情况下插入行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1049625/
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
SQL-How to Insert Row Without Auto incrementing a ID Column?
提问by SpoiledTechie.com
I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column.
我有一个表，它有一个强制自动增量列，这个列是一个非常有价值的 ID，在整个应用程序中都保留了下来。很抱歉，将它作为自动递增列对我来说开发很差。
So, here is the problem. I have to insert into this table an ID for the column that has already been created and removed from the table. Kind of like resurrecting this ID and putting it back into the table.
所以，这就是问题所在。我必须将已创建并从表中删除的列的 ID 插入到该表中。有点像复活这个 ID 并将它放回表中。
So how can I do this programatically do this without turning the column increment off. Correct me if I am wrong, if I turn it off programatically, It will restart at 0 or 1 and I don't want that to happen...
那么如何在不关闭列增量的情况下以编程方式执行此操作。如果我错了，请纠正我，如果我以编程方式将其关闭，它将在 0 或 1 重新启动，我不希望这种情况发生...
回答by Charles Bretana
If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:
如果您使用的是 Microsoft SQL Server，则可以通过发出语句来“关闭”自动递增功能Set Identity_Insert [TableName] On，如下所示：
Set Identity_Insert [TableName] On
-- --------------------------------------------
Insert TableName (pkCol, [OtherColumns])
Values(pkValue, [OtherValues])
-- ---- Don't forget to turn it back off ------
Set Identity_Insert [TableName] Off
回答by marc_s
In addition to Charles' answer (which is now 100% correct :-) and which preserves the current value of the IDENTITY on the table), you might also want to check the current value of an IDENTITY on a table - you can do this with this command here:
除了 Charles 的回答（现在 100% 正确 :-) 并且保留了表上 IDENTITY 的当前值），您可能还想检查表上 IDENTITY 的当前值 - 您可以这样做在这里使用这个命令：
DBCC CHECKIDENT('YourTableName')
If you ever need to actually changeit, you can do so by using this command here:
如果您需要实际更改它，您可以在此处使用此命令：
DBCC CHECKIDENT ('YourTableName', RESEED, (new value for IDENTITY) )
回答by Donald Lancaster
Actually, the code above for INDENTITY_INSERT is correct - turning it ON tells the server you want to insert the values yourself. It allows you to insert values into an IDENTITY column. You then want to turn it back off (allowing the server to generate and insert the values) when you are done.
实际上，上面的 INDENTITY_INSERT 代码是正确的 - 将其打开会告诉服务器您要自己插入值。它允许您将值插入到 IDENTITY 列中。然后，您希望在完成后将其关闭（允许服务器生成并插入值）。
回答by Drishya Nair
bulk insert tablename from 'C:\test.csv' with (rowterminator = '\n',fieldterminator = ',',KEEPIDENTITY)
