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))
)
This question is related to
sql
sql-server
stored-procedures
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
. . .
)
Source: Stackoverflow.com