| Author |
Topic |
|
OMEN42
Starting Member
8 Posts |
 Posted - 2014-06-11 : 11:35:07
|
| Hi everybody..Thanks for give me the opportunity to ask my first question in this forum:)I am new at SQL programming and I have a question to a query that has to be executed within Management Studio 2008 R2:I have an existing table called Company, with several columns like Id, Name and Language. I want to add a new row with a new Id, Name but with an existing language setting.I am able to execute but the first error message is "Unable to parse query text".After clicking the OK button it creates a new row but with an another Id.new row: Id:78; Name:20 Language: enGBQuestions: why I got this error message and wrong ID?Please find the query below:SELECT Id, Name, LanguageFROM CompanyINSERT INTO Company (Id, Name, Language)VALUES ('62', '20', 'en-GB')edit: the error message from 2008 :Msg 544, Level 16, State 1, Line 3Cannot insert explicit value for identity column in table 'Company' when IDENTITY_INSERT is set to OFF.Should I set it to on? If yes, how? ;)Thank you in advance, greetings Sven |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-11 : 11:53:43
|
| Why are you adding an explicit value for the Id column? Don't you want SQL to auto-increment it? If you want it to auto-increment, then use this instead:INSERT INTO Company (Name, Language)VALUES ('20', 'en-GB')Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-06-11 : 11:53:56
|
| You have your ID column set to auto increment set to Identity column, this will self populate and acts as the Primary key.Why do you need to create a new ID when its set to Identity, if you need another id field for something else you should create a new column and leave the Identity column be.This will add to 2 columns other than Identity column.SELECT Id, Name, LanguageFROM CompanyINSERT INTO Company (Name, Language)VALUES (20, 'en-GB')We are the creators of our own reality! |
|
|
|
OMEN42
Starting Member
8 Posts |
Posted - 2014-06-11 : 12:11:54
|
| Thank you all for the simultaneous answers:)At the first try I did not add an explicit value and it auto-increments to 78 instead of 62.That was the reason for adding an explicit value.I have tried your query and the following error message popped up:61 row(s) affected)Msg 545, Level 16, State 1, Line 3Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.EDIT: SELECT Id, Name, LanguageFROM CompanyINSERT INTO Company (Name, Language)VALUES ('20', 'en-GB')Strange now it works without error message, but it increments to 80,81,82, and so onEDIT2: I am afraid that I have to delete the rows comletely and only the data... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-11 : 12:41:30
|
| I don't understand what issue you are having. It is doing what it is supposed to do: incrementing. Why do you need a particular row to be value 62?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
OMEN42
Starting Member
8 Posts |
Posted - 2014-06-11 : 12:47:39
|
Yes but it increments to 89,90,91, .....but the last seen row is line 61 Id=61 the next row after executing the query is line 62, ID=89Is it possible that there are hidden rows to be delete?[url=http://www.fotos-hochladen.net/view/capture50gdofmru6.png] [/url |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-11 : 12:48:53
|
| No hidden rows. It's because data was deleted or transactions were rolled back. You will have gaps with deletes and rollbacks. This is normal. Identity values do not have to be sequential.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
OMEN42
Starting Member
8 Posts |
Posted - 2014-06-11 : 13:04:04
|
| Do you think it is irreversible? I need to have the right order in my table like 62,63,64...otherwise I have to restore my back up database;)Unfortunately the is ot working:(Thanks for your great help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-11 : 13:06:44
|
quote:
Originally posted by OMEN42
Do you think it is irreversible? I need to have the right order in my table like 62,63,64...otherwise I have to restore my back up database;)Unfortunately the is ot working:(Thanks for your great help
You should not be using the identity option then. You will have to manage the id column via code, but it is not as scalable as the identity option. I would seriously rethink why can't have gaps.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
OMEN42
Starting Member
8 Posts |
Posted - 2014-06-11 : 13:20:55
|
| I just want to start from scratch, to be sure that we will have a clear database. It was only the first step of several queries related to this database and it is a good time to restore it;)I dont understand what do you mean with manage the id column via code?Is it possible to modify via code the gap?For us it is mandatory to have continuous Id numbers...I am afraid that I will also create a new topic for the next query but it makes really fun to play around with SQL Thank you Tara! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-11 : 13:25:19
|
| If you need contiguous numbers, then you can't use the identity option. Remove the identity option. To manage the id yourself, you'll need to start a transaction, lookup the max id and lock it (UPDLOCK, HOLDLOCK I believe), add 1 to that number and then insert the new row. Once all of that is complete, you can then commit the transaction. Because there are multiple steps and you are locking the current max id, this solution is not very scalable. Trust me. I have a system like this. Oh how I wish they'd take the time to convert it an identity and fix all of the respective code!Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
OMEN42
Starting Member
8 Posts |
Posted - 2014-06-12 : 10:04:32
|
| Great Support Tara! Thanks for your help. Step by step I discover the mighty world of SQL ;)I will start a new request for the last query of this process!Greetings, Sven |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
SQL query INSERT
Tara Kizer