如何将一条记录插入带有IDENTITY列的表中

Is it possible to insert only one record in one column on a table?

是否可以在表格中的一列中仅插入一条记录？

I have a table EMP_MASTERTBL with columns

我有一个表EMP_MASTERTBL与列

MASTERID | USERID | EMPNO | LASTNAME | FIRSTNAME | REGION | COUNTRY | ENTITY | LOCATION | JOBTITLE

I need to add a new job title in one particular employee so I used this code/query

我需要在一个特定的员工中添加一个新的职位，所以我使用了这个代码/查询

INSERT INTO EMP_MASTERTBL (MASTERID, JOBTITLE) VALUES ('7634', 'Manager')

But I got this error message

但是我收到了这条错误消息

Cannot insert explicit value for identity column in table 'EMP_MASTERTBL' when IDENTITY_INSERT is set to OFF.

当IDENTITY_INSERT设置为OFF时，无法在表'EMP_MASTERTBL'中为标识列插入显式值。

I only need to add another job title to one particular employee. Employee can have multiple job titles.

我只需要为一个特定的员工添加另一个职位。员工可以拥有多个职位。

Thank you.

谢谢。

4 个解决方案

#1

In SQL Server Studio, run this command first before the INSERT statement. You must have ALTER permission on this table to run this.

在SQL Server Studio中，首先在INSERT语句之前运行此命令。您必须具有此表的ALTER权限才能运行此权限。

SET IDENTITY_INSERT EMP_MASTERTBL ON;

#2

don't put value to MASTERID because it will be automatically generated. try this:

不要为MASTERID赋值，因为它会自动生成。尝试这个：

INSERT INTO EMP_MASTERTBL (JOBTITLE) VALUES ('Manager')

#3

It may not be possible especially when you have defined the columns as not null. The best way to add the title would be using the update statement like this: UPDATE EMP_MASTERTBL SET JOBTILE = 'Manager' WHERE USERID = 'EMPLOYEE'S_USERID'; NB: Avoid the quotes on userid if the userid is an integer.

特别是当您将列定义为非null时，可能无法实现。添加标题的最佳方法是使用如下更新语句：UPDATE EMP_MASTERTBL SET JOBTILE ='Manager'WHERERE USERID ='EMPLOYEE'S_USERID';注意：如果userid是一个整数，请避免在userid上使用引号。

#4

SET IDENTITY_INSERT EMP_MASTERTBL ON;
GO
INSERT INTO EMP_MASTERTBL (MASTERID, JOBTITLE) VALUES ('7634', 'Manager')
GO
SET IDENTITY_INSERT EMP_MASTERTBL OFF;

#1