February 1, 2012 at 6:50 am
Hi all,
i have a table city having these values
Id Name
1 ABC
2 yz
3 Etc
4 etccc
i deleted last three records like 2,3 and 4.now when i insert new record it give me id = 5
as now i have only one record of id=1 so i want next record to be id=2...how it could be?
February 1, 2012 at 6:54 am
That's how IDENTITY columns work. Read more here.
If you really want to reset the seed value, read up on DBCC Checkident.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 1, 2012 at 7:17 am
You can do it as follows,
1. SET IDENTITY_INSERT Table_name ON
2. INSERT into Table_Name (Column_1, Column_2......) Values (Value_1, Value_2.....)
(make sure while inserting you specify name of required columns including the ID, and specify the value)
3. SET IDENTITY_INSERT Table_name OFF
(After inserting the required ID, turn the INSERT_IDENTITY OFF)
February 1, 2012 at 7:34 am
rishiextc (2/1/2012)
You can do it as follows,1. SET IDENTITY_INSERT Table_name ON
2. INSERT into Table_Name (Column_1, Column_2......) Values (Value_1, Value_2.....)
(make sure while inserting you specify name of required columns including the ID, and specify the value)
3. SET IDENTITY_INSERT Table_name OFF
(After inserting the required ID, turn the INSERT_IDENTITY OFF)
Ah yes - forgot about that 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 1, 2012 at 8:41 am
Be very careful when using IDENTITY_INSERT note what may occur if you are not very careful
CREATE TABLE #City(Id INT IDENTITY(1,1),[Name] VARCHAR(10))
INSERT INTO #City
SELECT 'ABC' UNION ALL
SELECT 'yz' UNION ALL
SELECT 'Etc'
SET IDENTITY_INSERT #City ON
INSERT INTO #City(Id,[Name])
SELECT 1, 'xxx'
SET IDENTITY_INSERT #City OFF
SELECT id,[Name] FROM #City ORDER BY Id
SET IDENTITY_INSERT #City OFF
/*Result -Note the duplicate Id values
1ABC
1xxx
2yz
3Etc */
February 1, 2012 at 8:46 am
Seems like too much effort to me. If you are using identity why do you care what number it is? What are you going to do when you 10k rows and you want to delete row 1435? Do you deal with the fact that your identity (and likely primary key) is no longer sequential? How do you handle it? Do you update all other rows in the table to its current value minus 1? What about foreign keys that rely on this value? Do you now have to go update all those other tables? How are you going to do that? Beginning to see all the complications with fiddling with it? Is it really worth it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2012 at 4:54 am
Before going further make sure this column is not referenced by a FK.
If there are a FK pointing to that column dont touch it!
If you realy want to repopulate the column use DBCC Checkident or drop recreate the column.
Finally, this behavior is not a issue.
Read more about identity and unique id concepts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply