Change Indentity Increment

I need to change the seed and increment for an identity field in an existing
table and would like to make the change in a script rather then in Enterpris
e
Manager. I understand that I can use DBCC CHECKIDENT to reseed the field.
Is there a function to change the identity seed? My question applies to MS
SQL 2000.
Thanks in advance for any help.
JeremySounds scary!
Without thinking, one way to do it is to move the data off to a temporary
location, truncate your table, alter the ID column to be identity(x,y), and
then repopulate it (in the appropriate order).
I repeat my earlier comment - sounds scary!
Rob|||Yeah, especially if you have any foreign key constraints set up...
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:4F74A9C4-559C-49FF-A36E-159CBF2022AF@.microsoft.com...
> Sounds scary!
> Without thinking, one way to do it is to move the data off to a temporary
> location, truncate your table, alter the ID column to be identity(x,y),
> and
> then repopulate it (in the appropriate order).
> I repeat my earlier comment - sounds scary!
> Rob|||Jeremy - do you mind explaining WHY you want to do such a thing?|||Thanks for the reply Rob.
One the requirements I've been given for this database project is that new
identity values in TableA should be even, and in TableB should be odd.
"Rob Farley" wrote:

> Jeremy - do you mind explaining WHY you want to do such a thing?|||What about the records that currently exist? Are you going to divvy them up
between even records in one table, odd in the other? Or are you going to
re-number all existing records? This whole thing sounds a little painful
from here...
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
> Thanks for the reply Rob.
> One the requirements I've been given for this database project is that new
> identity values in TableA should be even, and in TableB should be odd.
> "Rob Farley" wrote:
>|||Thanks for the reply. The identity values for existing rows would remain th
e
same. New records for TableA would have an even identity value with a new
seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
increment 2 to have odd identity values for new records.
"Michael C#" wrote:

> What about the records that currently exist? Are you going to divvy them u
p
> between even records in one table, odd in the other? Or are you going to
> re-number all existing records? This whole thing sounds a little painful
> from here...
>
> "jeremy@.nospamwardlawclaims.com"
> < jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
> news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
>
>|||OK. Do both of these tables currently exist, and do both currently have
both even and odd identity values in them? Would it be easier to just add
another column to your PK (CHAR(1) possibly) indicating different identity
value sources? This solution just sounds a little shaky... what happens if
they add a third table in the future? Re-define all identities to +3
beginning with record x,xxxx?
Thanks.
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:3603F85C-41FB-4930-8E7A-6822E763058F@.microsoft.com...
> Thanks for the reply. The identity values for existing rows would remain
> the
> same. New records for TableA would have an even identity value with a new
> seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
> increment 2 to have odd identity values for new records.
> "Michael C#" wrote:
>|||To me it sounds like you actually want a view, with an insert trigger. The
trigger puts the inserted values into either TableA or TableB, depending on
which one has the higher ID field. But the view shows all of them.
This is the type of thing you might do if you wanted to set up replication
scenarios, or divide the tables between two separate databases and devices.
I think you create the new tables from scratch. Create the view (which is
just a 'select * from TableA union all select * from TableB') and the
trigger, and then populate the view from your old table. Let the trigger
handle the distribution of records.
Of course, you will need to make sure that the trigger handles the situation
where there are a whole bunch of rows in the 'Inserted' table. But there are
easy ways around this.
Rob