[rajib_banerjee]

Can I reset Identity column after truncating table.

--rajib

[willy_and_the_ci]

create table foo(c1 numeric(5,0) IDENTITY, c2 datetime)
go
insert into foo(c2) values(getdate())
go 5

select * from foo
go
c1 c2
------- --
1 Jun 21 2004 10:21AM
2 Jun 21 2004 10:21AM
3 Jun 21 2004 10:21AM
4 Jun 21 2004 10:21AM
5 Jun 21 2004 10:21AM


truncate table foo
go
set identity_insert foo on
go

insert into foo(c1,c2) values(100,getdate())
go
select * from foo
go
c1 c2
------- --
100 Jun 21 2004 10:23AM

identity_insert foo off
go

[rajib_banerjee]

Thanks for your reply.
Actually I need to reset the identity values as 1 not 100.

When I reset the value as 1, next entry is not becoming 2.

1> insert into foo values (getdate())
2> go 5
(1 row affected)
1> select * from foo
2> go
c1 c2
-------- --------------------------
100 Jun 21 2004 10:48:14:423AM
101 Jun 21 2004 10:49:13:383AM
102 Jun 21 2004 10:49:41:023AM
103 Jun 21 2004 10:49:41:023AM
104 Jun 21 2004 10:49:41:026AM
105 Jun 21 2004 10:49:41:026AM
106 Jun 21 2004 10:49:41:030AM

(7 rows affected)
1> truncate table foo
2> go
1> set identity_insert foo on
2> go
1> insert into foo(c1,c2) values(1,getdate())
2> go
1> select * from foo
2> go
c1 c2
-------- --------------------------
1 Jun 21 2004 10:50:31:583AM

(1 row affected)
1> set identity_insert foo off
2> go
1> insert into foo values(getdate())
2> go 5
(1 row affected)
1> select * from foo
2> go
c1 c2
-------- --------------------------
1 Jun 21 2004 10:50:31:583AM
107 Jun 21 2004 10:51:06:603AM
108 Jun 21 2004 10:51:06:606AM
109 Jun 21 2004 10:51:06:606AM
110 Jun 21 2004 10:51:06:610AM
111 Jun 21 2004 10:51:06:610AM

(6 rows affected)



Thanks
Rajib