title: Inserting Rows and Updating a Table without using SQL Cursor
body: <p>I'm trying to create a MS SQL 2005 stored procedure to allocate stock from a warehouse for a particular component in an order. There are multiple batches of stock available and these have to be used in a particular order. I can loop through the available stock and allocate until the order is fulfilled but I'm trying to think more set based than sequential and avoid using a CURSOR.</p>
  
  <p>Here is my query that gets the warehouse stock available for a particular order component;</p>
  
  <pre><code>SELECT 
          STOCK.ComponentId,
          STOCK.StockId,
          STOCK.ExpiryDate, 
          STOCK.BatchNo, 
          STOCK.StockQty, 
          ORDER_ITEMS.OrderQty        
      FROM 
          STOCK 
          JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId 
      WHERE 
          STOCK.WarehouseId = @WarehouseId 
          AND STOCK.StockQty &gt; 0
          AND ORDER_ITEMS.OrderItemId = @OrderItemId
  </code></pre>
  
  <p>I've been putting this into a temp table or creating a CTE with the query and applying an ORDER BY clause to get the stock sorted as it needs to be used up. This gives me a result set like:</p>
  
  <pre><code>ComponentId | StockId | ExpiryDate | BatchNo  | StockQty | OrderQty
  -------------------------------------------------------------------
  359         | 3107    | 2013-10-01 | 132435-1 | 20       | 50
  359         | 3215    | 2013-10-01 | 154558-1 | 100      | 50
  359         | 3216    | 2014-01-01 | 154689-1 | 100      | 50
  </code></pre>
  
  <p>What I need to do is insert records into a <code>STOCK_ALLOCATED</code> table using as many of the batches of stock as necessary to fulfil the Order quantity. In the example above I would use all 20 from the first row and then need 30 from the second row. </p>
  
  <p>This would need to insert two records into a <code>STOCK_ALLOCATED</code> table with the <code>OrderItemId</code>, <code>StockId</code> and the quantity (20 and 30) for the two used batches and also decrease the stock quantity in the <code>STOCK</code> table appropriately.</p>
  
  <p>Assuming that the necessary transactions are in place to maintain the stock tables consistently is there a way to do the inserts and updates without using a CURSOR to loop through and track how much stock I've already allocated and how much I still require?</p>