How to assign values to primary key table?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

SQL

insert into Student values(12,'jh','hjf',GETDATE())

What I have tried:

Here Am going insert values to a table. It display the error message like.
An explicit value for the identity column in table 'Student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Posted 31-Jan-18 18:37pm

Member 13650918

Updated 1-Feb-18 2:33am

Add a Solution

Comments

Jörgen Andersson 1-Feb-18 4:51am

Solution 1 is answering your actual question.
But the question we need to ask is: Why do you want to set the ID in the identity column manually?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-02-01T02:02:00

Solution 1 does not solve your problem, you also need to specify the names of the columns you want to populate with the insert.

For example:

SQL

SET IDENTITY_INSERT #Student on
insert into #Student (id, firstname, surname, joindate) values(5,'jh','hjf',GETDATE())
SET IDENTITY_INSERT #Student off

But as Jörgen Andersson states in his comment to your question - why do you want to insert a specific value for an identity column? It's not good practice.

Good practice is always to list the columns you are going to populate - it saves major reworks if there are changes to the table schema for a start. It also makes it more obvious that you are not going to include the identity column :-) This is better:

SQL

insert into #Student (firstname, surname, joindate) values('jh1','hjf1',GETDATE())

What you may not have noticed is the effect of your override to the identity column. If you query my data after the two updates above I get

5	jh	hjf	2018-02-01
6	jh1	hjf1	2018-02-01

SQL will not fill in that gap you created.
Worse than that, if I accidentally use an id that is already on the table I will not get any warning! This code

SQL

SET IDENTITY_INSERT #Student on
insert into #Student (id, firstname, surname, joindate) values(5,'jh3','hjf3',GETDATE())
SET IDENTITY_INSERT #Student off

results in

5	jh	hjf	2018-02-01
6	jh1	hjf1	2018-02-01
5	jh3	hjf3	2018-02-01

If I have any processing that relies on id being unique, it's going to break. If I don't have any reason to have id as a unique number then why define it as an identity column in the first place?

Karthik_Mahalingam · Answer 2 · 2018-01-31T18:57:00

Solution 1

try

SET IDENTITY_INSERT Student on
insert into Student values(12,'jh','hjf',GETDATE())
SET IDENTITY_INSERT Student off

Please read these
SET IDENTITY_INSERT (Transact-SQL) | Microsoft Docs[^]
How to turn IDENTITY_INSERT on and off using SQL Server 2008? - Stack Overflow[^]

Posted 31-Jan-18 18:57pm

Karthik_Mahalingam