January 18, 2013 at 12:39 am
I HAVE TWO TABLES
TBL_CH :
ID AMOUNT
1 10.20
2 20.42
3 30
TBL_account:
ID AMOUNT
1 8
1 2
1 1
2 16
2 4
3 25
3 4
I NEED TO CHECK that FOR each id the SUM showuld be equal IN BOTH tables.
so i fire following query:
SELECT id, amount- amount1 AS diff
SELECT a.id ,
a.amount ,
(SELECT SUM(amount)
FROM TBL_account WHERE id = a.id) amount1
FROM TBL_CH a
WHEN i find diffrence THEN i know what amount need TO be INSERTED IN TABLE
so a WRITE a PROC AND THEN TAKE a CURSOR WITH above uery AND simply INSERT . everthing is ok.
but WHEN at production the dba deployed it two TIME errorneously AND we got It know AFTER 2 weeks.
there is NO back , kindly suggest me the fix.
January 18, 2013 at 1:42 am
Why use a cursor?
You already have what you want to insert into the table, so just wrap the select into an insert statement, unless I am missing something in your problem
DECLARE @TBL_CH TABLE (ID INT, AMOUNT DECIMAL(18,2))
INSERT INTO @TBL_CH VALUES
(1,10.20),
(2,20.42),
(3,30)
DECLARE @TBL_ACCOUNT TABLE (ID INT, AMOUNT DECIMAL(18,2))
INSERT INTO @TBL_ACCOUNT VALUES
(1,8),
(1,2),
(1,1),
(2,16),
(2,4),
(3,25),
(3,4)
SELECT CH.ID, (CH.AMOUNT - SUM(AC.AMOUNT)) AS Difference
FROM @TBL_CH CH
INNER JOIN @TBL_ACCOUNT AC
ON CH.ID = AC.ID
GROUP BY CH.ID, CH.AMOUNT
INSERT INTO @TBL_ACCOUNT
SELECT CH.ID, (CH.AMOUNT - SUM(AC.AMOUNT)) AS Difference
FROM @TBL_CH CH
INNER JOIN @TBL_ACCOUNT AC
ON CH.ID = AC.ID
GROUP BY CH.ID, CH.AMOUNT
SELECT CH.ID, (CH.AMOUNT - SUM(AC.AMOUNT)) AS Difference
FROM @TBL_CH CH
INNER JOIN @TBL_ACCOUNT AC
ON CH.ID = AC.ID
GROUP BY CH.ID, CH.AMOUNT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply