Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: Need to have 2 auto increment columns (Seed, Increment)

    Hello,

    I Have a table that needs to have 2 unique number.

    detail_id and detail_print_id.

    detail_id is already an IDENTITY.

    both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).

    So I thought i could do the following:

    Code:
    detail_id INT NOT NULL IDENTITY(1,2),
    detail_print_id INT NOT NULL IDENTITY(2,2),
    --blah blah
    that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.

    is there anyway I can create a int column to auto increment, without the column being an IDENTITY??

    also, I would prefer to not have to create a second table with a single column just for this work.

    Thanks,
    Justin
    Last edited by freefall; 12-11-07 at 21:05.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Coach me a bit here, how does having the second surrogate key (detail_id versus detail_print_id) help?

    I don't understand what this does for you. although working just from the description that you've given it would certainly be easy enough to kludge. My problem is that I can't see how it helps, so I think I've somehow missed some important part of what you want.

    -PatP

  3. #3
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by Pat Phelan
    Coach me a bit here, how does having the second surrogate key (detail_id versus detail_print_id) help?

    I don't understand what this does for you. although working just from the description that you've given it would certainly be easy enough to kludge. My problem is that I can't see how it helps, so I think I've somehow missed some important part of what you want.

    -PatP
    The problem is this table is used to insert into another systems batch import table.

    And for this import table, I need to add the same row twice, just with different LINE_TYPE value, (L or P)...

    So when i insert the first row, the detail_id is used to populate the LINE_ID value. when i go to insert it again, using detail_id as the LINE_ID, but this time with a LINE_TYPE of p, i get an error because LINE_ID already exists.

    So my thinking was, when adding the second row, use the print_id to populate the LINE_ID value, so it is different.

    I know this is not the cleanest approach, but given that its an external finance system that I am inserting into, I cannot change its internals.

    Cheers

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not a "kludge" at all, this can be accomplished easily using what's known as a computed column
    Code:
    create table two_identities
    ( detail_id INTEGER NOT NULL IDENTITY(1,2)
    , detail_print_id AS detail_id + 1
    , detail_foo VARCHAR(937)
    )
    insert into two_identities (detail_foo) values ( 'curly' )
    insert into two_identities (detail_foo) values ( 'larry' )
    insert into two_identities (detail_foo) values ( 'moe' )
    insert into two_identities (detail_foo) values ( 'shemp' )
    insert into two_identities (detail_foo) values ( 'joe' )
    insert into two_identities (detail_foo) values ( 'curly joe' )
                                   
    select * from two_identities
    
    detail_id  detail_print_id  detail_foo
         1          2           curly
         3          4           larry
         5          6           moe  
         7          8           shemp
         9         10           joe
        11         12           curly joe
    vwalah, exactly what you wanted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by r937
    not a "kludge" at all, this can be accomplished easily using what's known as a computed column
    Code:
    create table two_identities
    ( detail_id INTEGER NOT NULL IDENTITY(1,2)
    , detail_print_id AS detail_id + 1
    , detail_foo VARCHAR(937)
    )
    insert into two_identities (detail_foo) values ( 'curly' )
    insert into two_identities (detail_foo) values ( 'larry' )
    insert into two_identities (detail_foo) values ( 'moe' )
    insert into two_identities (detail_foo) values ( 'shemp' )
    insert into two_identities (detail_foo) values ( 'joe' )
    insert into two_identities (detail_foo) values ( 'curly joe' )
                                   
    select * from two_identities
    
    detail_id  detail_print_id  detail_foo
         1          2           curly
         3          4           larry
         5          6           moe  
         7          8           shemp
         9         10           joe
        11         12           curly joe
    vwalah, exactly what you wanted
    Awesome, thats exactly what I wanted. I was writing a function to execute with a trigger, but this solution is wayy better.

    Cheers, I wish there was a rate post feature in the forum.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yep, Rudy was thinking exactly what I was thinking.

    I was just going to save you the trouble and clutter of declaring the computed column and suggest that you simply add one to the column on the second import.

    As long as you've got an answer that works for you, that's plenty good enough for me!

    -PatP

Posting Permissions

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