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
1,065 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 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 66

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

635 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