IDENTITY ??

[Komil13]

how would i reset a identity using a sql statement or within a proc??

 

[RickCole]

DBCC CHECKIDENT
( 'table_name' [, { NORESEED | {RESEED [, new_reseed_value]} }]
)

This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO


Rick.

 

[Komil13]

this doesn't work if there are records in the table ... is there any other way ??

 

[RickCole]

My apologies it was something I didn’t have chance to test. Try using the DBCC statement after running the function 'SET IDENTITY_INSERT tablename ON' it might then work. I have not had chance to test this also.

You will need to run 'SET IDENTITY_INSERT tablename OFF' afterwards.

If this doesn't work can you explain what you are trying to achieve in more detail.

Rick.

 

[tlbroadbent]

Read about "DBCC CHECKIDENT" in SQL Books Online. The article explains how to reset seed values under certain conditions such as having data in the table.

SQL Server documentation can also be found at...

http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_5lv8.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.