Post reply

Auto generation of Identity column

  • June 27, 2011 at 10:43 pm

    #96412

    Dear All,

    I need to generate the Identity column value from max value of my own.

    Please suggest how to generate the identity column

    select I.*,C.* --into Map5days_interactions_callnotes

    from callnotes_MSD_AU C,Interactions_MSD_AU I

    where c.CONTACT_ID = I.HCP_ID

    and C.NOTE_DT between dateAdd(dd, -5, I.Interaction_date) and dateAdd(dd, 5, I.Interaction_date)

    and C.Team_Name = I.TeamName

    and C.territory_position = I.callBy

  • June 28, 2011 at 2:04 am

    #1344997

    If you really need an identity column and you simply want it to start from a value other than 1, you can set it up as

    IDENTITY [ ( seed , increment ) ]

    where seed is the value to start from.

    I suspect that you're looking for a sequence table instead. Unless you have a very good reason for this, I would recommend avoiding that strategy. However, there's an excellent article here that could help you.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • July 22, 2011 at 4:14 am

    #1356910

    Just to emphasize...

    Generating additional ID's in a column using MAX will frequently lead to duplicated numbers if there's no unique constraint on the column and will lead to frequent rollbacks if there is. If you absolutely cannot use a very safe IDENTITY column, then do use the "Sequence Table" method in the link that Gianluca provided. Whatever you do, don't use the MAX method that so many people will suggest you use. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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