Post reply

Consecutive numbering WITHOUT identity

  • November 23, 2006 at 3:54 pm

    #153311

    Hi,

    I'm trying to insert records from one table into another table. The destination table has a ROWID field which cannot be an identity key, but needs to 'act like' an identity key and have its value populated with (Max(ROWID) + 1) for each row added to the table.

    To my thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not work as it will only be evaluated once so if I am adding 1000 records and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of 1235.

    Is there a way to accomplish this?

    Thanks

  • November 23, 2006 at 5:41 pm

    #673741

    Create #Table with IDENTITY column, populate it with new data and than insert from #Table to destination table with Max(ROWID) + #Table.ID

    _____________
    Code for TallyGenerator

  • November 24, 2006 at 4:22 pm

    #673865

    The destination table has a ROWID field which cannot be an identity key, ...

    Why the heck not?

    --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)
    Intro to Tally Tables and Functions

  • November 28, 2006 at 10:52 pm

    #674580

    use a correleated subquery

    for instance:

    id | grouping

    select grouping, (select count (*) from table b where b.id < a.id and a.grouping = b.grouping)

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

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