resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an INT field that auto increments by 1, now that I am approaching
the
> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have an INT field that auto increments by 1, now that I am approaching
> the
> > end of the testing phase, I need to delete all the records and reset the
> > "count" to 1.
> >
> > Any help?
> >
> > Mitch
> >
> >
>|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Just truncate the table (or drop and recreate it) since you don't need
the
> > data. Truncate will reset the identity.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I have an INT field that auto increments by 1, now that I am
approaching
> > the
> > > end of the testing phase, I need to delete all the records and reset
the
> > > "count" to 1.
> > >
> > > Any help?
> > >
> > > Mitch
> > >
> > >
> >
> >
>|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> > Sorry, I'm kind of new to SQL server, what does "truncate the table"
mean
> > and how do I do it?
> >
> > SQL Server 2000
> >
> > Thanks!
> >
> > Mitch
> >
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > > Just truncate the table (or drop and recreate it) since you don't need
> the
> > > data. Truncate will reset the identity.
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > I have an INT field that auto increments by 1, now that I am
> approaching
> > > the
> > > > end of the testing phase, I need to delete all the records and reset
> the
> > > > "count" to 1.
> > > >
> > > > Any help?
> > > >
> > > > Mitch
> > > >
> > > >
> > >
> > >
> >
> >
>

resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi,
> I have an INT field that auto increments by 1, now that I am approaching

---

the

quote:

---

> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>

---

|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> the
>

---

|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...

quote:

---

> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
the[QUOTE]
approaching[QUOTE]
the[QUOTE]
>

---

|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
mean[QUOTE]
> the
> approaching
> the
>

---

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.

Reset Sequential Numbering

I have a table that is updated on a regular interval. I need to set an
overall ID that increments by 1, such as 0001, 0002, etc. I need a second ID
that does similarly, incrementing by 1 (such as 20001, 20002, 20003), but
which also resets to 20001 for each new update interval.
ThanksFor a heavy usage system, this could be costly.
Anyway, you could create an update trigger and increment the columns as
desired.
e.g.
create trigger _u on tb
for update
as
update tb
set colx=right(10001+cast(colx as int),4)
from tb,inserted i
where tb.pk=i.pk
go
-oj
"HollyylloH" <HollyylloH@.discussions.microsoft.com> wrote in message
news:AE05B6DD-30F4-45A3-94D3-AE1AD09BC31F@.microsoft.com...
>I have a table that is updated on a regular interval. I need to set an
> overall ID that increments by 1, such as 0001, 0002, etc. I need a second
> ID
> that does similarly, incrementing by 1 (such as 20001, 20002, 20003), but
> which also resets to 20001 for each new update interval.
> Thankssql