Post reply

Auto Increment RowID without the use of Auto Increment in the table

  • November 10, 2011 at 5:17 pm

    #390833

    Hello Everyone

    This is a real bad design, because it is supposed to be able to be used on multiple platforms. I know, very poor way, but I was over-ruled many times when I argued the point. But anyway.....

    I need to be able to add an auto increment value to a table variable table, that is used to insert data into a hard table. I query the data from one hard table into the table variable table that will be Inserted into a hard table that I must provide the Auto Increment Value

    I query a Keys table to select the next value in line to be used as a Auto Increment value. Lets say the number I select is 5

    I need to begin my auto increment on 6 and continue without knowing how many rows will be inserted into my table variable table. Some times it may be only 3, some times it could be 3000.

    I hope this makes sense. It took me a while to wrap my head around the fact that I could not use the SQL built in auto increment in the table.

    Thank you in advance for any and all help and suggestions.

    Andrew SQLDBA

  • November 10, 2011 at 5:47 pm

    #1406052

    SELECT @mid = MAX(id) from basetable.

    INSERT INTO basetable (fake identity, columns)

    Select ROW_NUMBER() OVER (ORDER BY ClusteredIndeColumnsOfBaseTable) + @mid as Fake identity, Columns FROM @tbl

    Then you need to choose the isolation level. Repeatable read makes sense here but I don't know if it's the best option.

    You could do a tablelock + hold lock as well until the insert finishes and you commit the transactions. => that part's not my forte!

  • November 10, 2011 at 6:16 pm

    #1406056

    Thank You so very much, that works perfectly.

    Andrew SQLDBA

  • November 11, 2011 at 4:12 am

    #1406172

    The query's the easy part. How did you test the concurrency part?

  • November 11, 2011 at 5:03 pm

    #1406620

    On this one, I got lucky, my SSIS package is the only thing that is performing INSERTs, so I do not have to worry too much about that.

    I think the one and only thing that has gone my way for this entire project.

    Thanks again

    Andrew SQLDBA

  • November 11, 2011 at 5:14 pm

    #1406621

    HTH 😉

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

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