By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Auto Generate Value

P: 92
Hi

Can anyone tell me, how can i set Identity specification as zero when it gets new entries in table after delete all the rows in d table.

now i m getting entries like, if i delete 2 rows in d table.
after i enter new values. it increment the value as 3,4..... like d.

but i need to enter 1,2,3...... after delete rows in d table

Thanks in Advance
Limno
Apr 18 '08 #1
Share this Question
Share on Google+
4 Replies


deepuv04
Expert 100+
P: 227
Hi

Can anyone tell me, how can i set Identity specification as zero when it gets new entries in table after delete all the rows in d table.

now i m getting entries like, if i delete 2 rows in d table.
after i enter new values. it increment the value as 3,4..... like d.

but i need to enter 1,2,3...... after delete rows in d table

Thanks in Advance
Limno
Hi,
use the command

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value)

thanks
Apr 18 '08 #2

P: 92
Hi,
use the command

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value)

thanks
Thank u 4 replying me,
i m new to sql, can u plz explain me more.
how can i do this while inserting records into d table.

i hv 3 fields.

1.code
2.short_acct
3.acct

(1. code) holds the identity specification field.

while inserting these fields. i hv to do the process. ie code starts the value as zero always. after deleted rows
Apr 18 '08 #3

deepuv04
Expert 100+
P: 227
Thank u 4 replying me,
i m new to sql, can u plz explain me more.
how can i do this while inserting records into d table.

i hv 3 fields.

1.code
2.short_acct
3.acct

(1. code) holds the identity specification field.

while inserting these fields. i hv to do the process. ie code starts the value as zero always. after deleted rows
Hi,
After deleting the rows from the table execute the following statement

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value or Initial value )

Then execute the insert statement

example: assuming the columns in the table as given
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. CREATE TABLE Sample_Table(code INT IDENTITY( 1,1),
  4. short_acct VARCHAR(100),
  5. acct VARCHAR(100) )
  6.  
  7. INSERT INTO [Sample_Table] ([short_acct],[acct])
  8. VALUES ('value1','value1')
  9. INSERT INTO [Sample_Table] ([short_acct],[acct])
  10. VALUES ('value2','value2')
  11. INSERT INTO [Sample_Table] ([short_acct],[acct])
  12. VALUES ('value3','value3')
  13.  
  14. SELECT * FROM [Sample_Table] 
  15.  
  16. DELETE FROM [Sample_Table] 
  17.  
  18. -- Reset the identity value 
  19. DBCC CHECKIDENT ([Sample_Table] , RESEED, 0)
  20.  
  21. SELECT * FROM [Sample_Table] 
  22.  
  23. -- insert new record
  24. INSERT INTO [Sample_Table] ([short_acct],[acct])
  25. VALUES ('value4','value4')
  26.  
  27. -- select to know which value iserted
  28. SELECT * FROM [Sample_Table] 
  29.  
Apr 18 '08 #4

P: 92
hi deepuv04

Thank u very much.

Itz working..........
Apr 18 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.