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
2
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
2
don't put value to MASTERID because it will be automatically generated. try this:
不要为MASTERID赋值,因为它会自动生成。尝试这个:
INSERT INTO EMP_MASTERTBL (JOBTITLE) VALUES ('Manager')
#3
0
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
0
SET IDENTITY_INSERT EMP_MASTERTBL ON;
GO
INSERT INTO EMP_MASTERTBL (MASTERID, JOBTITLE) VALUES ('7634', 'Manager')
GO
SET IDENTITY_INSERT EMP_MASTERTBL OFF;
#1
2
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
2
don't put value to MASTERID because it will be automatically generated. try this:
不要为MASTERID赋值,因为它会自动生成。尝试这个:
INSERT INTO EMP_MASTERTBL (JOBTITLE) VALUES ('Manager')
#3
0
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
0
SET IDENTITY_INSERT EMP_MASTERTBL ON;
GO
INSERT INTO EMP_MASTERTBL (MASTERID, JOBTITLE) VALUES ('7634', 'Manager')
GO
SET IDENTITY_INSERT EMP_MASTERTBL OFF;