• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

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.

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
SuperJinx
Asked:
SuperJinx
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
David ToddSenior DBACommented:
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
 
SuperJinxAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now