如何为主键表分配值？ [英] How to assign values to primary key table?

问题描述

insert into Student values(12,'jh','hjf',GETDATE())

我尝试了什么：



这里我要插入值到a表。它显示错误消息，例如。

只有在使用列列表并且IDENTITY_INSERT为ON时，才能指定表'Student'中标识列的显式值。

推荐答案

解决方案1无法解决您的问题，您还需要指定要使用插入填充的列的名称。



例如：

Solution 1 does not solve your problem, you also need to specify the names of the columns you want to populate with the insert.

For example:

SET IDENTITY_INSERT #Student on
insert into #Student (id, firstname, surname, joindate) values(5,'jh','hjf',GETDATE())
SET IDENTITY_INSERT #Student off

但正如JörgenAndersson在评论中指出的那样 - 为什么要插入？标识列的特定值？这不是一个好的做法。



好​​的做法总是列出你要填充的列 - 如果表格架构有变化，它会保存主要的返工。它也更明显你不将包括标识列:-)这是更好的：

But as Jörgen Andersson states in his comment to your question - why do you want to insert a specific value for an identity column? It's not good practice.

Good practice is always to list the columns you are going to populate - it saves major reworks if there are changes to the table schema for a start. It also makes it more obvious that you are not going to include the identity column :-) This is better:

insert into #Student (firstname, surname, joindate) values('jh1','hjf1',GETDATE())

您可能没有注意到的是覆盖对标识列的影响。如果你在上面的两个更新之后查询我的数据，我得

What you may not have noticed is the effect of your override to the identity column. If you query my data after the two updates above I get

5	jh	hjf	2018-02-01
6	jh1	hjf1	2018-02-01

SQL不会填写你创建的那个差距。

更糟糕的是，如果我不小心使用已经在桌面上的 id ，我将不会收到任何警告！此代码

SQL will not fill in that gap you created.
Worse than that, if I accidentally use an id that is already on the table I will not get any warning! This code

SET IDENTITY_INSERT #Student on
insert into #Student (id, firstname, surname, joindate) values(5,'jh3','hjf3',GETDATE())
SET IDENTITY_INSERT #Student off

结果

5	jh	hjf	2018-02-01
6	jh1	hjf1	2018-02-01
5	jh3	hjf3	2018-02-01

如果我有任何依赖于 id 的处理是唯一的，它就会破坏。如果我没有任何理由将 id 作为唯一编号，那么为什么要将其定义为标识列。第一个地方？

If I have any processing that relies on id being unique, it's going to break. If I don't have any reason to have id as a unique number then why define it as an identity column in the first place?

尝试

try

SET IDENTITY_INSERT Student on
insert into Student values(12,'jh','hjf',GETDATE())
SET IDENTITY_INSERT Student off

请阅读这些

SET IDENTITY_INSERT（Transact-SQL）| Microsoft Docs [ ^ ]

如何使用SQL Server 2008打开和关闭IDENTITY_INSERT？ - 堆栈溢出 [ ^ ]

这篇关于如何为主键表分配值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！