Results 1 to 3 of 3

Thread: Identity

  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: Identity

    Can I reset Identity column after truncating table.

    --rajib
    Rajib Banerjee

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    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
    Last edited by willy_and_the_ci; 06-20-04 at 21:43.

  3. #3
    Join Date
    Nov 2003
    Posts
    11
    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
    Rajib Banerjee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •