title: Add an incremental number in a field in INSERT INTO SELECT query in SQL Server
body: <p>I have an <code>INSERT INTO SELECT</code> query. In the <code>SELECT</code> statement I have a subquery in which I want to add an incremental number in a field. 
  This query will work fine if my <code>SELECT</code> query and returns only one record, But if it returns multiple rows it inserts the same number in the incremental field for all those rows. 
  Is there any way to restrict it to add an incremental number every time?</p>
  
  <pre><code>INSERT INTO PM_Ingrediants_Arrangements_Temp 
  (AdminID,ArrangementID,IngrediantID,Sequence)
  (SELECT 
       @AdminID, @ArrangementID, PM_Ingrediants.ID, 
       (SELECT 
            MAX(ISNULL(sequence,0)) + 1 
        FROM
            PM_Ingrediants_Arrangements_Temp 
        WHERE 
            ArrangementID=@ArrangementID)
  FROM 
      PM_Ingrediants 
  WHERE 
      PM_Ingrediants.ID IN (SELECT 
                                ID 
                            FROM 
                                GetIDsTableFromIDsList(@IngrediantsIDs))
  )
  </code></pre>