CODEWITHSUNDEEP
sql-servervb.net
chobowski
chobowski

Reputation: 109

How to insert one record into a table with an IDENTITY column

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

I have a table EMP_MASTERTBL with columns 

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.

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

Thank you.

Upvotes: 0

Views: 172

Answers (4)

Nolan Shang
Nolan Shang

Reputation: 2328

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

Upvotes: 0

Romeo
Romeo

Reputation: 129

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

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

Upvotes: 2

DVT
DVT

Reputation: 3127

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

SET IDENTITY_INSERT EMP_MASTERTBL ON;

Upvotes: 2

David
David

Reputation: 511

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.

Upvotes: 0

Related Questions