[dba-SQLServer] Building tables

Jim,

Assuming you have the necessary permissions, I find:

DBCC CHECKIDENT ('table_name', RESEED, 0)

works well for reseeding the identity value.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Jim Lawrence
> Sent: Wednesday, August 10, 2005 12:42 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Building tables
> 
> 
> Hi All:
> 
> I have been porting a legacy database into a new MS SQL set 
> of tables. In
> the process the data is added and removed several times as 
> improvements are
> made on the table structures and the DTS is tested. Each time 
> the import
> process is re-ran the auto ID identity values, of course, continue to
> increment.
> 
> The field can be re-set manually by changing the ID field 
> type, clearing the
> table content and changing the field type back; all very 
> labour intensive.
> Is there a simple way, in code, to initialize the auto ID 
> identity values so
> it re-start at the initial seed value? 
> 
> MTIA
> Jim