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,036 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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 75
SQL Server 2012 r2 - Sum totals 2 31
sql 2016 Integration Service connecting to 2012 3 49
Substring works but need to tweak it 14 31
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 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