Increment? 1

[dBjason]

Hello,

I have a table I need to self-insert of sorts. Problem is, there's a sort-order field that's NOT an identity column. But it needs to be incremented as I insert.

What I'm trying to do is (something like) this:

insert into mytable (var1, var2, sortorder)
select var1, var2, sortorder + 1
from mytable where var1 = 137

The max sortorder in the table is currently 6010. There are about 2000 records it should re-insert, starting with 6011, 6012...

Any ideas? I'd hate to write 2000 single insert statements with values...

Thanks!
-Jason

 

[markros]

SQL 2005+ solution:

insert into myTable (var1, var2, SortOrder)

select var1, var2, SortOrder + row_Number() over (order by SortOrder) from myTable where var1 = 137

PluralSight Learning Library