Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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: bad
good
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
   ) 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.
 
To explore more Click here
 
Note:- Table should not have referntial integrity..
 
Thank you
  Permalink  
Comments
damodara naidu betha at 25-Oct-12 5:54am
   
Please post them in this site.
damodara naidu betha at 26-Oct-12 3:58am
   
which one? Can you send me the link please?
Rate this: bad
good
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.
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 25 Oct 2012
Copyright © CodeProject, 1999-2014
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