Hi,
    I have the following problem with the Identity column. Will be glad if someone could help me solve this issue.

Table : Test
Identity Field Name : No1
Field Name : Name1

Initially I insert 5 records so the value in the field No1 is
1
2
3
4
5

Due to errors I delete records where No1 is 4 and 5 so now I have only records where the value of No1 is 1 , 2 and 3.
I insert 2 records afresh. The value assigned to No1 is 6 and 7 but then I want the value to be 4 and 5. Please let me know if this is possible. If yes, how should I be doing this.

Thanks is advance.

I dont think this is possible.

_________________________
- Vijay G
Strive for Perfection

Hi

You can use checkident to force the identity value. e.g.

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

You will either need appropriate permissions to run this.

Regards

Nickie

CHECKIDENT will reseed the identity value, but it will not "fill in" the missing values caused by deletions (or INSERTS whose transaction was aborted) which is what the OP was looking for.

There is no way to do this with an identity value.

I can't resist asking why the OP would want to do this. (If I had to guess, this sounds like an attempt to use an identity as a row number, and that is doomed to fail.)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi,
I dont know if this is what you want.

You can have following script to explicitly insert into identity column.So you can fill in the deleted ones.But then the basic purpose of using identity is defeated

SET IDENTITY_INSERT Test ON
INSERT INTO test (No1,Name1) values (1,'test')
SET IDENTITY_INSERT Test OFF

Dont know why you want to do this, but anyways you can work around the above query

Amit

You can always drop the identity column, as long as it is not involved in a realationship, and then recreate it