Solved

SQL insert into query - error message An explicit value for the identity column in table 'Person' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Posted on 2013-01-17
4
989 Views
Last Modified: 2013-01-21
Hi, I am completely new to this and this is my first question, so i hope someone can help me please can someone give some examples or if it is a link to a web page please only send me tried and tested links as i am teaching my self SQL and would really appreciate avoiding even more unncesecary web pages if possible!

I am an absolute beginner at the moment and have been dabbling in SQL for the first time today using the w3 schools website tutorials i am currently following one of the tutorials which is an 'Insert INTO' tutorial.

Basically, I have written

 Insert INTO Person VALUES (12,'Jack', 'Lumsdon', '8 Drove Road', 'Birmingham')

12 being the next id in my table as my id's go up to 11

I am getting this error message and no value is being inserted into my table which is a basic FirstName, LastName, Address, City table.

'An explicit value for the identity column in table 'Person' can only be specified when a column list is used and IDENTITY_INSERT is ON.'

Can anyone shed some light or help me solve this? (I am using SQL Server 2008 on windows 8 which so far has been an absolute nightmare as it seems that it is not fully compatible as of yet, if anyone could also maybe shed light on why i keep getting error messages when i try to null a field in my tables it tells me i can not save?)

Apologies for the three questions in one but as i go to post this question i notice that i can offer a certain amount of points, what is the reasonable amount of points to offer per question as you can see i am an absolute novice!)

Much appreciated!!

SuperJinx
0
Comment
Question by:SuperJinx
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 100 total points
ID: 38789690
Here's the deal dude...

An Identity column is basically a counter (1, 2, 3, ..) that auto-magically populates with the next assigned number in the sequence every time you INSERT a new record.

Executing the below T-SQL will add the row to your table, with an ID of 12.

Insert INTO Person (FirstName, LastName, Address, City)
VALUES ('Jack', 'Lumsdon', '8 Drove Road', 'Birmingham')

Open in new window


Also, it's considered best practice to spell out the column names in the INSERT block.

The SET IDENTITY_INSERT applies only if you wish to hard-slam a specific identity number.
Microsoft error messages are not abundantly user-friendly.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 55 total points
ID: 38791871
Hi,

jimhorn is right.

your insert
insert INTO Person VALUES ( 12, 'Jack', 'Lumsdon', '8 Drove Road', 'Birmingham' )
is specifying an explicit value - you have to specify a value for each column in the table since you aren't specifying a column list.

As jimhorn stated, for this and other reasons it eases a lot of things if you specify the columns.
insert dbo.Person ( FirstName, LastName, Address, City )
values( 'Jack', 'Lumsdon', '8 Drove Road', 'Birmingham' )

Now try
insert dbo.Person( FirstName ) values( 'Jack' )

The identity should supply the value for the ID column, and the other columns should be null, or you get an error message that you need to specify a value or can't use null or something like that.

Now try
insert dbo.Person( FirstName ) values( 'Jimmy' ), ('Fred' ), ( 'Teddy' )

With SQL 2008 and above, you can specify multiple rows - commas between brackets holding the values.

HTH
  David
0
 

Author Closing Comment

by:SuperJinx
ID: 38802632
Cheers guys very helpful, gave more points to jimhorn as he was the first to answer and David your answer was also very helpful in reenforcing Jim's point although i didnt quite understand your point    'The identity should supply the value for the ID column, and the other columns should be null, or you get an error message that you need to specify a value or can't use null or something like that.'<br /><br />Either way thanks alot guys!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38803352
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Find correct record using  ;WITH 7 18
sQL pivot 9 39
how to fix this error 14 46
sqlseverexpress 2008 agent xps question 1 11
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now