sql - Iterate through a view inserting to a table -

i have table of customer orders. need insert products purchase order if exist on correlating invoice. quite few things rely on data purchase order table can build views assist insert.

view maxes, grabs max(_) + 1 of fields must increment each insert.

nextsalesorder   fifoid    changeid    nextpo    --------------------------------------------- 102515           300005    26665       1234      

i built view of products need inserted purchase order.

invoice       product    changeid --------------------------------- 102515        105        26665    102516        144        26665 102517        105        26665 

i need insert 3 of values purchase order table, changeid must increment each. cannot change table schema.

insert [purchase order] (invoice, product, changeid) select top(1) t1.invoice, t1.product, t2.changeid dbo.[invoice] t1, dbo.[maxes] t2 

the above code works fine long i'm using top(1), need way iterate through records of [invoice] view while incrementing changeid

i see 2 ways of achieveing need,

1. insert top(1) view purchase order table , iterate through view until rows have been inserted.

2. alter product view changeid increments on own.

i cannot dump view temporary table changeid not increment, iteration through top(1) necessary (unless knows how that).

i'm stuck here , calling procedure multiple times rows inserted, advice great.

thanks.

you can use ranking functions rank rows , use in query. if have changeid in invoices view, there no need make join maxes view

insert [purchase order] (invoice, product, changeid) select invoice       ,product       ,row_number() over(partition changeid order changeid) + changeid - 1 dbo.[invoice] 

this part row_number() over(partition changeid order changeid) + changeid - 1 gives row number each changeid add changeid , minus 1 result

---

sample usage

declare @tbl table (invoice int,       product int,    changeid int)  insert @tbl select 102515,        105  ,      26665 union  select 102516 ,       144 ,       26665 union  select 102517  ,      105,        26665  select * @tbl  select row_number() over(partition changeid order changeid) + changeid - 1 newchangeid        ,changeid @tbl 

output

newchangeid changeid 26665       26665 26666       26665 26667       26665