Hi PRA,
Refer th ebelow Query. If your id column is auto incremented then don't pass the id for inserting.
SQL
DECLARE @TblMonthly AS TABLE(Id INT,IdProduct INT,Number INT,Price INT,Summa INT)
INSERT INTO @TblMonthly VALUES(2,5,3,4,12),(3,3,5,3,15),(4,2,8,1,8),(5,6,4,2,8),(6,9,9,7,63),(7,1,5,3,15),(8,10,11,1,11)
DECLARE @TblYear AS TABLE(Id INT,IdProduct INT,Number INT,Price INT,Summa INT)
INSERT INTO @TblYear VALUES(2,5,4,4,16),(3,3,7,3,21),(4,2,8,1,12),(5,6,4,3,12),(6,9,9,8,72)
SELECT * FROM @TblMonthly
SELECT * FROM @TblYear
DECLARE @TotalMonthlyRecords INT, @Count INT
SET @TotalMonthlyRecords = (SELECT COUNT(*) FROM @TblMonthly)
SET @Count = 1
WHILE (@Count <= @TotalMonthlyRecords)
BEGIN
DECLARE @Id INT, @MonthIdProduct INT, @MonthNumber INT, @MonthPrice INT, @MonthSumma INT
SET @Id = (SELECT Id FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,* FROM @TblMonthly) t WHERE RowNo = @Count)
SET @MonthIdProduct = (SELECT IdProduct FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,* FROM @TblMonthly) t WHERE RowNo = @Count)
SET @MonthNumber = (SELECT Number FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,* FROM @TblMonthly) t WHERE RowNo = @Count)
SET @MonthPrice = (SELECT Price FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,* FROM @TblMonthly) t WHERE RowNo = @Count)
SET @MonthSumma = (SELECT Summa FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNo,* FROM @TblMonthly) t WHERE RowNo = @Count)
IF EXISTS(SELECT IdProduct FROM @TblYear WHERE IdProduct = @MonthIdProduct)
BEGIN
UPDATE @TblYear SET
Number = Number + @MonthNumber
,Summa = Summa + @MonthSumma
,Price = Price + @MonthPrice
WHERE IdProduct = @MonthIdProduct
END
ELSE
BEGIN
INSERT INTO @TblYear VALUES(@Id,@MonthIdProduct,@MonthNumber,@MonthPrice,@MonthSumma)
END
SET @Count = @Count + 1
END
SELECT * FROM @TblYear
Screenshot
