SQL 在 Access 中使用自动编号 - INSERT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/771485/
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
Using Autonumbering in Access - INSERT statements
提问by Smashery
I'm having trouble running an INSERTstatement where there's an autonumber as the PK field. I have an Auto-incrementing longas the Primary Key, and then 4 fields of type double; and yet Access (using ADO) seems to want five values for the insert statement.
我在运行INSERT将自动编号作为 PK 字段的语句时遇到问题。我有一个自动递增long作为主键，然后是 4 个类型的字段double；然而 Access（使用 ADO）似乎需要插入语句的五个值。
INSERT INTO [MY_TABLE] VALUES (1.0, 2.0, 3.0, 4.0);
>> Error: Number of query values and destinations fields are not the same.
INSERT INTO [MY_TABLE] VALUE (1, 1.0, 2.0, 3.0, 4.0);
>> Success!!
How do I use Autonumbering to actually autonumber?
如何使用自动编号来实际自动编号？
回答by Frederik Gheysels
If you do not want to provide values for all columns that exists in your table, you've to specify the columns that you want to insert. (Which is logical, otherwise how should access, or any other DB, know for which columns you're providing a value)?
如果不想为表中存在的所有列提供值，则必须指定要插入的列。（这是合乎逻辑的，否则应该如何访问或任何其他数据库知道您为哪些列提供值）？
So, what you have to do is this:
所以，你需要做的是：
INSERT INTO MyTable ( Column2, Column3, Column4) VALUES ( 1, 2, 3 )
Also , be sure that you omit the Primary Key column (which is the autonumber field). Then, Access will set it to the next value by itself.
此外，请确保您省略了主键列（这是自动编号字段）。然后，Access 会自行将其设置为下一个值。
You can then retrieve the primary-key value of the newly inserted record by executing a
然后，您可以通过执行以下命令来检索新插入记录的主键值
SELECT @@identity FROM MyTable
statement.
陈述。
回答by Bhushan Bhangale
Mention the column names in your query as you are providing only 4 values whereas you have 5 columns in that table. Database need to know the value you providing is for which column.
在您的查询中提及列名，因为您仅提供 4 个值，而该表中有 5 个列。数据库需要知道你提供的值是针对哪一列的。
回答by Tony Toews
My understanding though is that if you are using SQL Server or similar and there are triggers which add additional records the @@IDENTITY may be that of the other additional records.
我的理解是，如果您使用的是 SQL Server 或类似的东西，并且有添加额外记录的触发器，@@IDENTITY 可能是其他额外记录的触发器。
回答by user3310546
Just Leave the Auto Number Out of the Insert Query. It will populate on it's own.
只需将自动编号排除在插入查询之外。它将自行填充。
There is an ID field before the ProjectID
ProjectID前有一个ID字段
INSERT INTO ProjectRiskAssessment
( ProjectID
, RiskClass
, RiskElement
, RiskAttribute
, RiskQuestion
, RiskScale
, RiskStatus
, RiskSeverity
, RiskProbability
, RiskResponse )
SELECT
1 AS Expr2
, PullRiskAssessmentQuestions.RiskClass
, PullRiskAssessmentQuestions.RiskElement
, PullRiskAssessmentQuestions.RiskAttribute
, PullRiskAssessmentQuestions.RiskQuestion
, '0' AS Expr3
, 'Open' AS Expr4
, '1' AS Expr5
, '1' AS Expr6
, ' ' AS Expr7
FROM PullRiskAssessmentQuestions;
