Post reply

reset id for new record inserted

  • February 17, 2009 at 12:13 am

    #155281

    Hi,

    I do data migration whereby the new records inserted ,

    see as example below:

    customerid serialno product

    00001 1 milk

    00001 2 sugar

    00001 3 sauce

    00002 1 milk

    00002 2 sauce

    00003 1 burger

    00003 2 durian

    So how to solve this?

    thanks in advace

  • February 17, 2009 at 12:33 am

    #944986

    Hello,

    I would suggest you need to explain your problem more.

    Just in case I have correctly guessed what you need: Truncate Table resets an Identity column to its seed value.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • February 17, 2009 at 3:03 am

    #945034

    Hi sory bcos got missing word on my previous post.

    basically,I facing problem to maintain serialno field to be reset

    when i insert for new customerid.

    To be clear I give example as below for what i want ,whereby you can

    see first customerid is 00001 serialno is generate continously start from 1 then when

    come to new customerid 00002 it reset to no 1 back.So i want to keep for each different

    customerid will get serialno generate from 1 back.

    customerid serialno product

    00001 1 milk

    00001 2 sugar

    00001 3 sauce

    00002 1 milk

    00002 2 sauce

    00003 1 burger

    00003 2 durian

    please advice to me and it really appreciate if got sample to do like this:)

    thanks in advace

  • February 17, 2009 at 5:31 am

    #945098

    Hello again,

    I suspect what you are after is the Row_Number function.

    Possibly something like:-

    Select

    customerid ,

    Row_Number() Over(Partition By customerid Order By customerid Asc) As serialno,

    Product

    From

    YourTable

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • February 17, 2009 at 1:10 pm

    #945423

    [font="Verdana"]You won't be able to do this with an identity field, as it generates a unique number for every row. You will need to manually generate the numbers. The row_number() as suggested should do it.

    If you also need to generate the customerid number (hopefully not) then you will need to come up with another approach. I can think of several ways to solve this, but I would need to see what the key values are that control when you know how to move to a new outermost number.

    [/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply