Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
1,099 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 300 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 165 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 66

Expert Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

705 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