Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: how to reseed the identity column

    Hi ,

    As per requirement i need to start the identity column with 1000 and increment by 1.

    i have tried the following code but its not working.
    CREATE TABLE ORDERS(
    OrderID int identity [(100,10)] NOT FOR REPLICATION,
    CustID int,
    OrderDate datetime
    )

    Can some one shed light on the proper syntax for the above problem.

    Thanks,
    Lahari.

  2. #2
    Join Date
    May 2011
    Posts
    28
    Hi,
    Correct syntax would be
    CREATE TABLE ORDERS(
    OrderID numeric(38,0) identity ,
    CustID int,
    OrderDate datetime
    )
    go
    Please refer to the link below for more details:
    Transact-SQL User's Guide

  3. #3
    Join Date
    May 2011
    Posts
    28
    Hi,
    I think you have to insert manually first setting identity_insert option on for the table ORDERS as explained in the link below:
    SyBooks Online

    1> set identity_insert ORDERS on
    2> go
    1> insert into ORDERS(OrderID,CustID,OrderDate) values (1000,1,getdate())
    2> go
    (1 row affected)
    1> select * from ORDERS
    2> go
    OrderID CustID OrderDate
    ----------------------------------------- ----------- --------------------------
    1000 1 Jul 13 2011 9:37PM

    (1 row affected)
    1> insert into ORDERS(CustID,OrderDate) values (1,getdate())
    2> go
    Msg 585, Level 16, State 1:
    Server 'PKD', Line 1:
    Explicit value must be specified for identity field in table 'ORDERS' when IDENTITY_INSERT or IDENTITY_UPDATE is ON.
    1> set identity_insert ORDERS off
    2> go
    1> insert into ORDERS(CustID,OrderDate) values (2,getdate())
    2> go
    (1 row affected)
    1> select * from ORDERS
    2> go
    OrderID CustID OrderDate
    ----------------------------------------- ----------- --------------------------
    1000 1 Jul 13 2011 9:37PM
    1001 2 Jul 13 2011 9:37PM

    (2 rows affected)
    1>

Tags for this Thread

Posting Permissions

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