Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Unanswered: incremental counter

    I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.

    insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code,
    0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10))
    then 'Prior' else 'Current' end ),
    left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)),
    0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)),
    ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*),
    (case when isnull(b.location,'') = '' then '' else b.location end) as location
    from mbi_tran_temp a
    left join enrollees b on a.ssn = b.ssn and
    ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))

    The '999+count(*)' is where I would like to have the incremental value.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    OK. If you consider the following small example, what values would you like the third column to contain?

    The first column, id, is an identity column.

    Code:
    id, value, newColumn
    -------------------------------
    1, Test, x
    2, Test2, y
    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2007
    Posts
    8
    Example..starting number is 999.
    Code:
    id, value, newColumn
    -------------------------------
    1, Test, x, 1000
    2, Test2, y,1001

    Only problem is when the identity number doesnt start out at 1..then what?

    Quote Originally Posted by r123456
    OK. If you consider the following small example, what values would you like the third column to contain?

    The first column, id, is an identity column.

    Code:
    id, value, newColumn
    -------------------------------
    1, Test, x
    2, Test2, y
    Regards,

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I just need to know one more fact. Does the 999 value, and should all subsequent values, relate in any way to the result set of the insert query? That is, using our example above, if the next insert statement will insert 500 rows, the new values for the newColumn will start from 1002?

    Will this be just a one off process or will it need to apply when the above statement is executed?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Nov 2007
    Posts
    8
    Yes. I will need to ultimately store the last result of that field in another table.

    start - 999

    first run
    row value
    ----------
    1 1000
    2 1001
    3 1002

    second run
    row value
    ----------
    1 1003
    2 1004
    3 1005

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Should your example not be,

    Code:
    ...
    second run
    row value
    -----------
    4, 1003
    5, 1004
    6, 1005
    What version of SQL Server are you using? I do kind of hope it's 2005...
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Nov 2007
    Posts
    8
    Just assume that the first result record always start with 1 and I want to add to another value to insert.

    Using 2000. Is there no system variable that I can pull from that I am not aware of?

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You can map the value of newColumn to the value of the current identity column in your table, by adding the defined offset (eg, 999) to the current value of the identity column when you insert new rows. Yes it may have gaps, but this would occur in any other solution you were to implement, unless you were to run a reconciliation type process after each and every update or delete operation.
    Last edited by r123456; 11-16-07 at 11:13.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Nov 2007
    Posts
    8
    Thanks for the info..but I cant have gaps in the resulting number as it is an invoice number.

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by kbrown3274
    Thanks for the info..but I cant have gaps in the resulting number as it is an invoice number.
    Easy. You will just have to prevent any delete operations on the table, and also prevent any process from updating the new sequence column.

    Regards,
    Last edited by r123456; 11-16-07 at 11:21.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least under GAAP, invoice numbers can't have duplicates but gaps in the numbering are no problem at all. That's no different than a wasted physical invoice form being discarded instead of filed. This happens all of the time, and only extremely high security installations insist on filing the wasted forms.

    -PatP

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If you need a way of generating the numbers that does not directly depend on the value of the current identity_column, then the simple solution will be to create a new table to store the initial starting value, and a function to return the next number and update the value in the table. This function can then be called from your insert statement. The advantage of this approach is that it supports multi-row insert statements.

    I do apologise for the delay in reaching a solution, but I found your initial post a little ambiguous.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    robert,

    this only works in systems with very little traffic. this will not work in high volume databases. you will get PK violations left and right.
    If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by Thrasymachus
    robert,

    this only works in systems with very little traffic. this will not work in high volume databases. you will get PK violations left and right.
    I believe it could work. It just certainly wouldn't perform in a high traffic environment.

    I myself, would not use it at all. Instead I would map it to the identity_insert column and use a simple addition expression. However, noting that the original post included a multi-row insert as an example, I thought it appropriate to consider this in my reply.

    Regards,
    Last edited by r123456; 11-16-07 at 12:21.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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