SQL 在“插入表值（）”语句中处理标识列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/935252/
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
Handling identity columns in an "Insert Into TABLE Values()" statement?
提问by James McMahon
In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following?
在 SQL Server 2000 或更高版本中，在使用如下语句时是否可以处理自动生成的主键（标识）列？
Insert Into TableName Values(?, ?, ?)
My goal is to NOT use the column names at all.
我的目标是根本不使用列名。
回答by Eric
By default, if you have an identity column, you do notneed to specify it in the VALUES section. If your table is:
默认情况下，如果你有一个标识列，你就不会需要在值部分指定它。如果您的表是：
ID NAME ADDRESS
Then you can do:
然后你可以这样做：
INSERT INTO MyTbl VALUES ('Joe', '123 State Street, Boston, MA')
This will auto-generate the ID for you, and you don't have to think about it at all. If you SET IDENTITY_INSERT MyTbl ON, you can assign a value to the ID column.
这将自动为您生成 ID，您根本不必考虑它。如果是SET IDENTITY_INSERT MyTbl ON，则可以为 ID 列分配一个值。
回答by Aaron Bertrand
Another "trick" for generating the column list is simply to drag the "Columns" node from Object Explorer onto a query window.
生成列列表的另一个“技巧”是简单地将“列”节点从对象资源管理器拖到查询窗口上。
回答by A-K
The best practice is to explicitly list the columns:
最佳做法是明确列出列：
Insert Into TableName(col1, col2,col2) Values(?, ?, ?)
Otherwise, your original insert will break if you add another column to your table.
否则，如果您向表中添加另一列，您的原始插入将中断。
回答by Mitch Wheat
You have 2 choices:
您有 2 个选择：
1) Either specify the column name list (without the identity column).
1) 要么指定列名列表（不带标识列）。
2) SET IDENTITY_INSERT tablename ON, followed by insert statements that provide explicit values for the identity column, followed by SET IDENTITY_INSERT tablename OFF.
2) SET IDENTITY_INSERT tablename ON，然后是为标识列提供显式值的insert语句，然后是SET IDENTITY_INSERT tablename OFF。
If you are avoiding a column name list, perhaps this 'trick' might help?:
如果您避免使用列名列表，也许这个“技巧”可能会有所帮助？：
-- Get a comma separated list of a table's column names
SELECT STUFF(
(SELECT
',' + COLUMN_NAME AS [text()]
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'TableName'
Order By Ordinal_position
FOR XML PATH('')
), 1,1, '')
回答by Tom H
Since it isn't practical to put code in a comment, in response to your comment in Eric's answer that it's not working for you...
由于将代码放在评论中是不切实际的，为了回应您在 Eric 的回答中的评论，它对您不起作用......
I just ran the following on a SQL 2005 box (sorry, no 2000 handy) with default settings and it worked without error:
我刚刚使用默认设置在 SQL 2005 框（抱歉，没有 2000 方便）上运行以下命令，并且没有错误：
CREATE TABLE dbo.Test_Identity_Insert
(
id INT IDENTITY NOT NULL,
my_string VARCHAR(20) NOT NULL,
CONSTRAINT PK_Test_Identity_Insert PRIMARY KEY CLUSTERED (id)
)
GO
INSERT INTO dbo.Test_Identity_Insert VALUES ('test')
GO
SELECT * FROM dbo.Test_Identity_Insert
GO
Are you perhaps sending the ID value over in your values list? I don't think that you can make it ignore the column if you actually pass a value for it. For example, if your table has 6 columns and you want to ignore the IDENTITY column you can only pass 5 values.
您是否可能将 ID 值发送到您的值列表中？我认为如果您实际上为它传递了一个值，则不能让它忽略该列。例如，如果您的表有 6 列，而您想忽略 IDENTITY 列，则只能传递 5 个值。
回答by sohan yadav
set identity_insert customer on
insert into Customer(id,Name,city,Salary) values(8,'bcd','Amritsar',1234)
where 'customer' is table name
其中“客户”是表名
