Add an incremental number in a field in INSERT INTO SELECT query in SQL Server

Question

I have an INSERT INTO SELECT query. In the SELECT statement I have a subquery in which I want to add an incremental number in a field. This query will work fine if my SELECT 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?

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)) ) 

Answer 1

You can use the row_number() function for this.

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)     SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,             row_number() over (order by (select NULL))     FROM PM_Ingrediants      WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)                              ) 

If you want to start with the maximum already in the table then do:

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)     SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,            coalesce(const.maxs, 0) + row_number() over (order by (select NULL))     FROM PM_Ingrediants cross join          (select max(sequence) as maxs from PM_Ingrediants_Arrangement_Temp) const     WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)                              ) 

Finally, you can just make the sequence column an auto-incrementing identity column. This saves the need to increment it each time:

create table PM_Ingrediants_Arrangement_Temp ( . . .     sequence int identity(1, 1) -- and might consider making this a primary key too     . . . )