Click here to Skip to main content
12,942,930 members (59,909 online)
Rate this:
Please Sign up or sign in to vote.
I have created a table in Sql.In that added 50 rows and when i delete 41 to 50 rows it gets deleted.But when i'm entering values for next row means it shows 51th row not 41th row.How to solve this one???

Posted 24-Oct-12 20:35pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Hi Bala,

You need to set the identity seed of your to 40 in using the following
console command.

DBCC CHECKIDENT (yourtable, reseed, 40)

After that start inserting the rows into your table. New rows starts with 41 as identity column.
Let me explain with an example. Create a table by using this script

CREATE TABLE [dbo].[ReseedTable](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Value] [char](1) NOT NULL

Now insert the data..
Insert Into ReseedTable VALUES('a'),('b'),('c'),('d'),('e'),

Now you have the data in your table like ..
Id     Value
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j

Delete last 5 rows
DELETE FROM ReseedTable WHERE Id > 5

Now the table has data like
Id     Value
1	a
2	b
3	c
4	d
5	e

Now set the identity seed value to 5 on you table using following console command
DBCC CHECKIDENT ('Reseedtable', reseed, 5)

Now start insert the records like
 Insert Into ReseedTable VALUES ('f'),('g'),('h'),('i'),('j')
 -- Now see all the data in your table
SELECT * FROM ReseedTable

You will get the result as
Id     Value
1   a
2   b
3   c
4   d
5   e
6   f
7   g
8   h
9   i
10  j

See.. it doesn't have greater than 10 in the Id column. Like this we can override the
identity property of a table.

To explore more Click here

Note:- Table should not have referntial integrity..

Thank you
damodara naidu betha 25-Oct-12 5:54am
Please post them in this site.
damodara naidu betha 26-Oct-12 3:58am
which one? Can you send me the link please?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Integer primary keys maintain the history of inserts even when deleted, this is so data is not lost when referencing other tables and keeps things unique.

If you want an incrementing counter like the one you described add a column to your table and update the contents your self.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 4,633
CHill60 2,880
Maciej Los 2,268
Jochen Arndt 1,900
ppolymorphe 1,755

Advertise | Privacy | Mobile
Web02 | 2.8.170518.1 | Last Updated 25 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100