12,942,930 members (59,909 online)
Rate this:
See more:
Hi,
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???

Regards
Balamurugan
Posted 24-Oct-12 20:35pm

Rate this:

## 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
) ON [PRIMARY]
```

Now insert the data..
```Insert Into ReseedTable VALUES('a'),('b'),('c'),('d'),('e'),
('f'),('g'),('h'),('i'),('j')
```

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.

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

Rate this:

## 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.

Top Experts
Last 24hrsThis month
 Maciej Los 160 OriginalGriff 133 ppolymorphe 130 CHill60 80 Frankie-C 80
 OriginalGriff 4,633 CHill60 2,880 Maciej Los 2,268 Jochen Arndt 1,900 ppolymorphe 1,755