Error message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error message

Author

Topic

isjord
Starting Member

38 Posts

Posted - 2006-01-08 : 19:49:50

I was doing a database for the first time using the query analyzer this is my results.

USE Registration
GO

INSERT INTO Student(studentID, studentName)
VALUES(38214, 'Letersky')
INSERT INTO Student(studentID, studentName)
VALUES(54907, 'Altvater')
INSERT INTO Student(studentID, studentName)
VALUES(66324, 'Aiken')
INSERT INTO Student(studentID, studentName)
VALUES(70542, 'Marra')

I got this as a result, I do not know what to change to make this work.
Cannot insert explicit value for identity column in table 'Student' when IDENTITY_INSERT is set to OFF.

How do I change this what does the error mean.
Thanks

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-08 : 20:15:51

You cannot insert into a column with identity. The Student table studentID column must have created with IDENTITY. An identity column means when a new row is inserted, SQL Server will provide a unique, incremental value for the column. So you code should be

INSERT INTO Student(studentName) VALUES('Letersky')
INSERT INTO Student(studentName) VALUES('Altvater')
INSERT INTO Student(studentName) VALUES('Aiken')
INSERT INTO Student(studentName) VALUES('Marra')

A value will be assign the the studentID column during INSERT.

If you need to explicitly insert value to the column studentID, you need to use SET IDENTITY_INSERT <table_name> ~~OFF~~ ON

SET IDENTITY_INSERT Student OFF ON
INSERT INTO Student(studentID, studentName)
VALUES(38214, 'Letersky')

For more information See the SQLServer Books OnLine on SET IDENTITY_INSERT and also CREATE TABLE

-----------------
'KH'
2006

isjord
Starting Member

38 Posts

Posted - 2006-01-08 : 22:07:31

This is what I put in and still got errors. What do I do now?

SET IDENTITY_INSERT Student OFF
INSERT INTO Student(studentID, studentName)
VALUES(38214, 'Letersky')
INSERT INTO Student(studentID, studentName)
VALUES(54907, 'Altvater')
INSERT INTO Student(studentID, studentName)
VALUES(66324, 'Aiken')
INSERT INTO Student(studentID, studentName)
VALUES(70542, 'Marra')

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-08 : 22:12:18

Sorry... it should be ON not OFF
SET IDENTITY_INSERT Student ON
INSERT INTO Student(studentID, studentName)
VALUES(38214, 'Letersky')

-----------------
'KH'
Selamat Hari Raya Haji

isjord
Starting Member

38 Posts

Posted - 2006-01-09 : 07:30:21

This is the error I get now. What should I do. I think it got something to do with when I made the ID the primary key. So how do I go back and change what I have done.

Thank you so much for your help.

Violation of PRIMARY KEY constraint 'PK__Student__76CBA758'. Cannot insert duplicate key in object 'Student'.
The statement has been terminated.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-09 : 07:40:56

quote:
An identity column means when a new row is inserted, SQL Server will provide a [red]unique/red], incremental value for the column

As highlighted before, SQL Server will ensure the identity column is unique which means no duplicates.

If you SET IDENTITY_INSERT ON, you have to ensure that the identity column (studentID) that you insert is not duplicated.

For example, using NOT EXISTS in the WHERE clause.

INSERT INTO Student(studentID, studentName)
select 38214, 'Letersky'
where not exists (select * from Student x where x.studentID = 38214)

Since you already have an identity column on the Student table, why do you still need to insert the identity value yourself ?

-----------------
'KH'
Selamat Hari Raya Haji

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-09 : 07:41:35

Usually a table has an identity column because you wanty the database to allocate the value.

INSERT INTO Student(studentName)
select 'Letersky'
select 'Letersky', scope_identity()
(this would also need a unique index on studentName probably)

If you allocate the values yourself then you need to make sure they are unique.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

isjord
Starting Member

38 Posts

Posted - 2006-01-09 : 18:54:39

I still do not understand. I changed to ON and still comes up with an error. What do I need to do now?

albanie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-09 : 19:25:35

Do not use IDENTITY_INSERT! Let SQL Server assign the appropriate identity value for you. To do this:

INSERT INTO Student(studentName) VALUES('Letersky')
INSERT INTO Student(studentName) VALUES('Altvater')
INSERT INTO Student(studentName) VALUES('Aiken')
INSERT INTO Student(studentName) VALUES('Marra')

Notice how the ID column is removed from the queries. This is on purpose. This means that SQL Server will manage it for you since you have the identity property turned on.

Tara Kizer
aka tduggan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-09 : 19:40:41

Do what Tara suggests but to help you understand what's happening...Look through and execute this code. You should be able to see why those 2 different errors are occuring.


set nocount on
use northwind

create table junk
	(junkIdentityColumn int identity(1,1) primary key
	,junkvalue int)
go

print '
insert a value to junkvalue and allow sql server to generate the identity value'
insert junk (junkvalue)
values (1)

print 'show our first row'
select * from junk

print '
we can override the auto generation of the identity value using identity_insert'
set identity_insert junk on
insert junk (junkIdentityColumn, junkvalue)
values (2,2)
set identity_insert junk off

print '
show junk with both rows'
select * from junk

print '
violate our primary key by inserting a duplicate value in the primary key'
set identity_insert junk on
insert junk (junkIdentityColumn, junkvalue)
values (2,3) --2 is already a value in the junkIdentityColumn column
set identity_insert junk off

print '
try to insert a value into an identity column without setting identity_insert on'
insert junk (junkIdentityColumn, junkvalue)
values (3,3) --explicit insert to identity column

print '
since junkIdentityColumn is an identity column we can allow sql server to generate the next value'
insert junk (junkvalue)
values (3) 
select * from junk

go
drop table junk

Be One with the Optimizer
TG

isjord
Starting Member

38 Posts

Posted - 2006-01-09 : 20:01:52

-- Create the Registration database
CREATE DATABASE Registration
GO

USE Registration
GO

-- Create the Student table in Registration
CREATE TABLE Student
(
studentId INT NOT NULL PRIMARY KEY IDENTITY(1,1), there’s something wrong with this
studentName VARCHAR(50) NOT NULL,
)
GO

USE Registration
GO

INSERT INTO Student(studentName) VALUES('Letersky')
INSERT INTO Student(studentName) VALUES('Altvater')
INSERT INTO Student(studentName) VALUES('Aiken')
INSERT INTO Student(studentName) VALUES('Marra')

Go

USE Registration
GO

SELECT * FROM Student
ORDER BY studentName ASC

I am trying to put in this:

Student_ID Student_Name
38214 Letersky
54907 Altvater
66324 Aiken
70542 Marra
STUDENT (Student_ID, Student_Name)

This is everything Iam trying for now to put in sql database, there is something about the ID number that is not right.

albanie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-09 : 21:53:19

isjord, if you need to assign value to the studentId yourself then don't create the column with IDENTITY

CREATE TABLE Student
(
studentId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
studentName VARCHAR(50) NOT NULL,
)

Then you can insert rows to the table using

INSERT INTO Student(studentID, studentName) VALUES(38214, 'Letersky')
INSERT INTO Student(studentID, studentName) VALUES(54907, 'Altvater')
INSERT INTO Student(studentID, studentName) VALUES(66324, 'Aiken')
INSERT INTO Student(studentID, studentName) VALUES(70542, 'Marra')

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources