Menu
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)) ) 
219
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
The Rank function can be used to generate a sequential number for each row or to give a rank based on specific criteria. The ranking function returns a ranking value for each row. However, based on criteria more than one row can get the same rank.
set @anyVariableName=0; select yourColumnName, @anyVariableName:=@anyVariableName+1 as anyVariableName from yourTableName; To understand the above syntax and set an increment counter, let us first create a table. The query to create a table is as follows. Insert some records in the table using insert command.
The syntax to create a sequence in SQL Server (Transact-SQL) is: CREATE SEQUENCE [schema.] sequence_name [ AS datatype ] [ START WITH value ] [ INCREMENT BY value ] [ MINVALUE value | NO MINVALUE ] [ MAXVALUE value | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE value | NO CACHE ]; AS datatype.
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 . . . ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
