Reseting automatic increments on primary keys

Hello
I've a SQL database that has several tables and relathionships between them.
Most of my tables have
the primary key set to automatic increments using identity on table properti
es.
It all did well in testing fases...
My problem is that when going to production how can I reset those identities
values on several primary keys to have my keys strat on 1 instead of the la
st number used in testing...
my procedure, that didn't worked, was first erasing all tables, second conve
rt all primary keys with identity set to no identity, third save table, fort
h put primary key back to identity increments, fifth save table again
The first record inserted, instead of having primary key with 1 brought prim
ary key with 80 or so...
how can I reset those values?!?!
best regards
Jorge RibeiroHi Jorge,
You can reset the current value of the identity column with:
DBCC CHECKIDENT('<table name>', RESEED, 1)
However, you shouldn't attach any meaning to the value of the identity
column for a number of reasons, so whether it starts at 1 or 80 shouldn't
make any difference.
Jacco Schalkwijk
SQL Server MVP
"Jorge Ribeiro" <jorge.ribeiro@.irsocial.mj.pt> wrote in message
news:99FB747F-DAED-4264-886C-FF5D28FDF426@.microsoft.com...

quote:

---

> Hello
> I've a SQL database that has several tables and relathionships between

---

them. Most of my tables have

quote:

---

> the primary key set to automatic increments using identity on table

---

properties.

quote:

---

> It all did well in testing fases...
> My problem is that when going to production how can I reset those

---

identities values on several primary keys to have my keys strat on 1 instead
of the last number used in testing...

quote:

---

> my procedure, that didn't worked, was first erasing all tables, second

---

convert all primary keys with identity set to no identity, third save table,
forth put primary key back to identity increments, fifth save table again

quote:

---

> The first record inserted, instead of having primary key with 1 brought

---

primary key with 80 or so...

quote:

---

> how can I reset those values?!?!
> best regards
> Jorge Ribeiro

---

|||Jacco is correct. You can use the CHECKIDENT function to reseed the
identity. However this will not change any of the current values. To do
that you will have to move the data to a different table, reseed the
identity and then import the data back in.
Rand
This posting is provided "as is" with no warranties and confers no rights.