I have three table FeesSetup, Fees, UserList.
FeesSetup Table has:
Tution Fee 50,
Exam Fee 30,
Other Fee 20
UserList Table:
All the userID stored in this table.
Fees Table:
Fees Generated here for all user
UserID FeesHead Fee
1 Exam Fee 30
1 Tution Fee 50
1 Other Fee 20
2 Exam Fee 30
2 Tution Fee 50
2 Other Fee 20
I successfully generated all the fees for every user using this code
INSERT INTO Fees( FeesTypeID,FeesSetupID,FeesHeadID,Fee,MonthID,InstituteID,UserID)
SELECT f.FeesTypeID,f.FeesSetupID,f.FeesHeadID,f.Fee,@MonthID,r.InstituteID,r.UserID
FROM UserList r,FeesSetup f
WHERE r.InstituteID = @InstituteID and f.InstituteID=@InstituteID and f.IsActive=1
But when new user added or fee change I try to regenerate fees, how can I regenerate fees?
I try this code to avoid duplicate issue but not working.
IF NOT EXISTS(SELECT * FROM Fees f, FeesSetup fs,Userlist u WHERE f.UserID= u.UserID and fs.FeesSetupID=f.FeesSetupID)
BEGIN
INSERT INTO Fees( FeesTypeID,FeesSetupID,FeesHeadID,Fee,MonthID,InstituteID,UserID)
SELECT f.FeesTypeID,f.FeesSetupID,f.FeesHeadID,f.Fee,@MonthID,r.InstituteID,r.UserID
FROM UserList r,FeesSetup f
WHERE r.InstituteID = @InstituteID and f.InstituteID=@InstituteID and f.IsActive=1
END
ELSE
BEGIN
UPDATE Fees SET .....
END
Anyone please help.
1条答案
按热度按时间qyzbxkaa1#
It looks like you could do this with
MERGE
.Note that the
ON
clause must contain only the joining conditions, ie all columns which uniquely identify a row in the target table. Do not include any filtering conditions there, put those into a view or CTE instead.