October 13, 2010 at 6:32 am
USE Database1;
CREATE TABLE Job_Titles
(Job_Title_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EE0_1_Classification VARCHAR(25),
Job_Title VARCHAR(27),
Job_Description VARCHAR(30),
Exempt_NonExempt_Status VARCHAR(1));
USE Database1;
INSERT INTO Job_Titles
(Job_Title_ID,EE0_1_Classification,Job_Title,Job_Description,Exempt_NonExempt_Status)
VALUES(1,'Office/Clerical','Stocker','Stock supplies','E'),
(2,'Sales Worker','Cashier','Accept incoming money','N')
544, Level 16, State 1, Line 5
Cannot insert explicit value for identity column in table 'Job_Titles' when IDENTITY_INSERT is set to OFF.
October 13, 2010 at 6:35 am
you cannot insert a value into the identity column. SQL will do that for you.
use;
USE Database1;
INSERT INTO Job_Titles
(EE0_1_Classification,Job_Title,Job_Description,Exempt_NonExempt_Status)
VALUES('Office/Clerical','Stocker','Stock supplies','E'),
('Sales Worker','Cashier','Accept incoming money','N')
October 13, 2010 at 6:36 am
As the error states you are attempting to insert an explicit value into the Job_Title_ID column.
As this is an identity column , you should not have to do this.
I would assume that what you want to do is :
INSERT INTO Job_Titles
(EE0_1_Classification,Job_Title,Job_Description,Exempt_NonExempt_Status)
VALUES(Office/Clerical','Stocker','Stock supplies','E'),
(2,'Sales Worker','Cashier','Accept incoming money','N')
October 13, 2010 at 8:19 am
Although, if you need to insert values into a table that has an identity column, you can make it possible using IDENTITY_INSERT
SET IDENTITY_INSERT myschma.mytable ON
Just remember to set it to OFF when you're done. You can only do this on one table at a time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply