How to reset IDENTITY?

[gus]

Martin V. wrote:
>
> Hello,
>
> Is there an easy way to reset the value of the IDENTITY property back
> to "1"?
>
> I can't find any alternative to dropping and recreating the table
> concerned. Yuck.
>
>
> Thanks,
>
> Martin V.
> <to reply, remove "antispam" from my address>

Try dropping and re-creating the table ;-)

gus

P.S. You will find yourself in all kinds of trouble if you expect to
have an uninterrupted sequence in an identity column. I recommend that
you read all the documentation related to identity columns in the
manuals (Chapter 3, Transact SQL Topics - IDENTITY Columns), and search
through dejanews for IDENTITY related articles in this group in the
past.

[Rob Verschoor]

Martin,

 

See the following article for information on this issue: www.euronet.nl/~syp_rob/idgaps.html

 

HTH,

 

Rob

--
----------------------------------------------------------------------
Rob Verschoor 

Certified Sybase ASE 11.5 Administrator (Professional)
Certified DBA/Performance & Tuning Specialist for Sybase System 11 

email r...@sypron.nl.*No*Spam*Please*
WWW www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------
 

Martin V. wrote in message ...

[Matt Van Rensburg]

Hi Martin

To reset the identity value for a table using an identity column you need
to use the following set command
set identity_insert {table_name} on
Then you must issue an insert against that table with the value you want
the identity column set to.
set identity_insert sales on
insert sales (syb_identity, col1) values (1, 'reset identity column')
Don't forget to turn identity_insert off once you are finished

HTH
Matt Van Rensburg
mat...@usa.net

Martin V. <mjlvan...@sympatico.ca> wrote in article
<Hltr2.4259$r%4.8...@news21.bellglobal.com>...
Hello,

Is there an easy way to reset the value of the IDENTITY property back to
"1"?

I can't find any alternative to dropping and recreating the table
concerned. Yuck.

Thanks,

Martin V.
<to reply, remove "antispam" from my address>

----------

[Rob Verschoor]

This will only work when you want to increase the identity value upwards. It
won't let you set it to a lower value. You need to drop the table first, and
then reset the value by inserting a value as you describe. Needless to say,
you don't want to just drop your table containing business data. Therefore,
you can use a seperate table for the key values alone. You can download a
stored procedure which does all this, plus an article explaining the whole
thing , from www.euronet.nl/~syp_rob/idgaps.html

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase ASE 11.5 Administrator (Professional)
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email r...@sypron.nl.*No*Spam*Please*
WWW www.euronet.nl/~syp_rob
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

Matt Van Rensburg wrote in message <01be4b03$0d40fae0$5f4c58d1@matman>...