I am looking for how to set the value of @MaxItemSeqNum to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement. I need this in order to insert the next record with the next sequence for that grouped item. I already have a surrogate primary key that auto-increments and is used to identify all items in the Table. Batch_ID can have duplicates and I group by these and then select the max of itemSeqNum to insert the next sequence itemSeqNum + 1. So if I have 5 records with the same batch_Id I am expecting to get 5 as a result and increment it by 1.
DECLARE @MaxItemSeqNum Int
SELECT Batch_Id,MAX(dbo.[Table1].[Item_Seq_number])
FROM Table1
WHERE Batch_Id = 1
Group By Batch_Id