Resetting current value of Identity

Resetting current value of Identity

Post by Hemo » Sun, 05 Aug 2001 12:41:22



Not sure if this is at all possible.  Can you reset the current value (e.g.,
the next value that will be used) for an Identity column?

I have a mapping table, and discovered a problem with maps above a certain
identity (e.g., 100).  I deleted out the problem rows (all rows with
Identity over 100).  However, I would like to not create a gap in my mapping
values, since I actually use the identity as part of the map value.
Typically this table never has rows deleted.  For certain reasons, I can not
remove the values of the first 100 entries, so I can't simply delete the
table and rebuild all the mapping.  The first 100 maps are valid.  I know I
could probably copy the table and data into a new temp table, delete the
current table, then copy data back in and set the seed of the Identity to
100.  However, I was hoping for something more straightforward in case this
ever happens again.  Is the Identity value for the column stored in some
system table that I can just change?  Thanks for any help.

 
 
 

Resetting current value of Identity

Post by BP Margoli » Sun, 05 Aug 2001 16:42:36


Check out the documentation on the DBCC CHECKIDENT command in the SQL Server
Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Not sure if this is at all possible.  Can you reset the current value
(e.g.,
> the next value that will be used) for an Identity column?

> I have a mapping table, and discovered a problem with maps above a certain
> identity (e.g., 100).  I deleted out the problem rows (all rows with
> Identity over 100).  However, I would like to not create a gap in my
mapping
> values, since I actually use the identity as part of the map value.
> Typically this table never has rows deleted.  For certain reasons, I can
not
> remove the values of the first 100 entries, so I can't simply delete the
> table and rebuild all the mapping.  The first 100 maps are valid.  I know
I
> could probably copy the table and data into a new temp table, delete the
> current table, then copy data back in and set the seed of the Identity to
> 100.  However, I was hoping for something more straightforward in case
this
> ever happens again.  Is the Identity value for the column stored in some
> system table that I can just change?  Thanks for any help.


 
 
 

Resetting current value of Identity

Post by Hemo » Sun, 05 Aug 2001 23:32:19


Perfect!  Exactly what I was looking for.  Thanks.


> Check out the documentation on the DBCC CHECKIDENT command in the SQL
Server
> Books Online.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Not sure if this is at all possible.  Can you reset the current value
> (e.g.,
> > the next value that will be used) for an Identity column?

> > I have a mapping table, and discovered a problem with maps above a
certain
> > identity (e.g., 100).  I deleted out the problem rows (all rows with
> > Identity over 100).  However, I would like to not create a gap in my
> mapping
> > values, since I actually use the identity as part of the map value.
> > Typically this table never has rows deleted.  For certain reasons, I can
> not
> > remove the values of the first 100 entries, so I can't simply delete the
> > table and rebuild all the mapping.  The first 100 maps are valid.  I
know
> I
> > could probably copy the table and data into a new temp table, delete the
> > current table, then copy data back in and set the seed of the Identity
to
> > 100.  However, I was hoping for something more straightforward in case
> this
> > ever happens again.  Is the Identity value for the column stored in some
> > system table that I can just change?  Thanks for any help.

 
 
 

1. Reset the current identity value

(6.5) Is it possible to reset the current identity value?  I looked at DBCC
CHECKIDENT but it only accepts one argument...the table name.

Somehow all our identity values were set back to their starting values in
the last day or so.  What might cause this?

--
J. M. "Papa Joe" De Moor
www.objectpac.com

2. S L O W - Grid with ODBC connection??

3. Resetting Identity Values.

4. DTS and Packed Fields

5. resetting value of identity column

6. SQL Query to MS-Access only returns single record

7. Resetting the Identity value

8. Limiting the files Getfile() can show

9. How to reset identity column value

10. reset identity value

11. Resetting the seed value of an identity column...

12. How reset IDENTITY start value?

13. Resetting identity field values