sql server programming: regarding reset identity column -- sql server programming

Re: regarding reset identity column

Jens K. Suessmeyer
3/11/2007 6:31:51 PM

Hi,

look in the BOL for DBCC CHECKIDENT (and the Reseed option)

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

[quoted text, click to view]

Re: regarding reset identity column

xyb
3/11/2007 6:31:56 PM

On 3=D4=C212=C8=D5, =C9=CF=CE=E78=CA=B151=B7=D6, Iter <I...@discussions.mic=
[quoted text, click to view]

what is the data type of the identity column,int or numeric?
i think you can copy all record to a new table with an indentity
column that can start at 1
use sql like this:
create table desttable
(
cid int indentity(1,0),
..=2E.)
insert into desttable select (other columns' value) from sourcetable

hope this help:)

Re: regarding reset identity column

Hari Prasad
3/11/2007 10:10:13 PM

Hello,

Use DBCC CHECKIDENT. See the below sample.

Eg:-
Forcing the current identity value to 30
The following example forces the current identity value in the Employee
table in the AdventureWorks database to a value of 30.

USE AdventureWorks;
GO
DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30);
GO

ThanksHari

[quoted text, click to view]

Re: regarding reset identity column

Uri Dimant
3/12/2007 12:00:00 AM

Iter

Is the identity column defined as a primary key? Have you looked at BIGINT
datatype?

[quoted text, click to view]

Re: regarding reset identity column

Iter
3/12/2007 6:42:02 AM

Thanks guys. This column is primary key. But this table is like staging table
and offen insert and delete. I am afraid it will have a day to be overflowed
in the future if I don't reset the value back from 1. for sure the DBCC
CHECKIDENT is not my requirement because it only reset the seed and I need to
reset the value from 1. Thanks.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.