title: Iterate through a view inserting to a table
body: <p>I have a table of customer orders. I need to insert products to a Purchase Order if they exist on the correlating Invoice. Quite a few things rely on data from the Purchase Order table so all I can do is build views to assist with the insert.</p>
  
  <p>View Maxes, grabs the <strong>MAX(_) + 1</strong> of fields that must increment with each insert.  </p>
  
  <pre><code>NextSalesOrder   FIFOID    ChangeID    NextPO   
  ---------------------------------------------
  102515           300005    26665       1234     
  </code></pre>
  
  <p>I built another view of products that need to be inserted to a purchase order.  </p>
  
  <pre><code>Invoice       Product    ChangeID
  ---------------------------------
  102515        105        26665   
  102516        144        26665
  102517        105        26665
  </code></pre>
  
  <p>I need to insert all 3 of those values to the Purchase Order table, but the ChangeID must increment for each. I cannot change the table schema. </p>
  
  <pre><code>INSERT INTO [Purchase Order] (Invoice, Product, ChangeID)
  SELECT TOP(1) T1.Invoice, T1.Product, T2.ChangeID
  FROM dbo.[Invoice] T1, dbo.[Maxes] T2
  </code></pre>
  
  <p>The above code works fine as long as i'm using TOP(1), but I need a way to iterate through all records of the [Invoice] view while incrementing the ChangeID</p>
  
  <p>I see 2 ways of achieveing what I need,</p>
  
  <ol>
  <li><p>Insert TOP(1) from the view to the Purchase Order table and iterate through the view until all rows have been inserted.  </p></li>
  <li><p>Alter the product view so the ChangeID increments on its own.</p></li>
  </ol>
  
  <p>I cannot dump the view into a temporary table as the ChangeID does not increment, so an iteration through Top(1) is necessary (unless someone knows how to do that).</p>
  
  <p>I'm stuck here and am currently just calling the procedure multiple times so all rows are inserted, any advice would be great.</p>
  
  <p>Thanks.</p>