Compare and Insert SUM value with Another Table using CURSOR in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

I have a stored procedure that works fine but I need to add more functions inside it and I do not know how to do it. I need to compare the sum of the accounts by id with the total of another table with the same account id and insert values in a table

SUM VALUES FROM TABLE 1 

Select ID , SUM(Total) AS Sum FROM TABLE1 GROUP BY ID

Record every register n Compare  with table2 

SELECT ID  FROM TABLE2  WHERE ID  = @id

and insert table VALIDATE 

INSERT INTO VALIDATE (ID,TOTALTAble1 , TOTALTABLE2 ) VALUES (@id , @sum , @totaltable2)

 stored procedure

DECLARE #R
CURSOR FOR
SELECT  ID, Sum(b) 
      FROM Ing
	  WHERE a = 2014
	  GROUP BY ID
OPEN #R
-- 
FETCH NEXT FROM #R INTO 
@id,@total, @name
 
-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN

INSERT INTO dbo.Total_In
SELECT @id,@totakl, @name

FETCH NEXT FROM #R INTO @id,@total, @name  
END
CLOSE #R
DEALLOCATE #R

Regards

You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago

Hey

SQL

DECLARE @Table1 AS TABLE(ID INT, Total INT)
DECLARE @Table2 AS TABLE(ID INT)
DECLARE @Total_In AS TABLE (totalid INT, totalsum INT) 
INSERT INTO @Table1 VALUES(1,100)
INSERT INTO @Table1 VALUES(2,400)
INSERT INTO @Table1 VALUES(3,300)
INSERT INTO @Table1 VALUES(4,200)
INSERT INTO @Table1 VALUES(4,200)

INSERT INTO @Table2 VALUES(1)
INSERT INTO @Table2 VALUES(3)
INSERT INTO @Table2 VALUES(2)
INSERT INTO @Table2 VALUES(4)

DECLARE @totalid INT
DECLARE @totalsum INT
DECLARE @id INT
SET @id = 4
DECLARE #R
CURSOR FOR
SELECT  SUM(T2.ID) ID, Sum(Total)
      FROM @Table1 T1
      INNER JOIN @Table2 T2 ON T2.Id=T1.Id
      WHERE T2.Id = @id
OPEN #R
-- 
FETCH NEXT FROM #R INTO
@totalid,@totalsum
-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN 
INSERT INTO @Total_In
SELECT @totalid,@totalsum 
FETCH NEXT FROM #R INTO @totalid,@totalsum
END
CLOSE #R
DEALLOCATE #R
SELECT * FROM @Total_In

Output

totalid totalsum
8 400