Okay, I think I get it now. I moved things around so I'm using the Row_number() + @new_position_id to insert into the new_position_id field in the temp table, and it's incrementing beautifully. As an example, when I run it the first time, the new position id I'm getting is 920781 and the last one is 920813.

The problem is, if I run it a second time, the first new position_id value it comes up with is 920782. I believe that the code that generates the new id's writes the id to a table so it knows what number to start with, so in our case, it's only generating one value.

I'm wondering if it's as easy as just updating that table with the final value that I generate with your code.

So:

run stored proc to get new position id

walk through the code to increment the code

use the last position id to run an insert statement to the table we're keeping the max value in

end