Click here to Skip to main content
14,298,696 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello,

I am using the below procedure to Insert data in a table if record not exists else update, but the Insert is working fine and the update over right the records, I don't know where I am doing wrong.

What I have tried:

USE [AnsiSchoolDB2]
GO
/****** Object:  StoredProcedure [dbo].[GenFeePayment]    Script Date: 3/9/2019 11:22:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROC [dbo].[GenFeePayment]
@cid int,
@pid int
	As
	BEGIN
	IF NOT EXISTS (SELECT * FROM tblFeePayment WHERE tblFeePayment.ClassID =@cid AND tblFeePayment.FeePartID =@pid)
  BEGIN
  INSERT INTO [dbo].[tblFeePayment]
           ([enrollID] ,[AdmissionNo] ,[classfee] ,ClassID ,[catdiscount],[studiscount] ,FeePartID ,[feeparticular]
) 

  SELECT  t.EnrollID,t.AdmissionNo, t.FeeAmount,t.ClassID,t.CatDiscountAmount,t.StuDiscountAmount,t.FeePartID,t.particular
  
  FROM

  (SELECT DiSTINCT tblEnrollment.EnrollID,tblStudent.AdmissionNo, tblStuCategory.CategoryID,tblStuCategory.Category,tblClass.ClassID,tblClass.ClassName,tblFeeParts.FeePartID,tblFeeParts.particular, tblClassFee.FeeAmount,
tblCatDiscount.Discount AS CatDiscount, tblCatDiscount.is_Percent AS CatPercent,
ISNULL((CASE WHEN tblCatDiscount.is_Percent = 1 THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100 
ELSE tblCatDiscount.Discount END), 0) 
                         AS CatDiscountAmount,
tblStuDiscount.Discount AS StuDiscount, 

tblStuDiscount.is_percent AS StuPercent, 

ISNULL((CASE WHEN tblStuDiscount.is_percent = 1 THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount) 
                         / 100 ELSE tblStuDiscount.Discount END), 0) AS StuDiscountAmount
  FROM [tblEnrollment]
  INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
  INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
  INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
  INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
  INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
  INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
  LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID ) t
  WHERE ClassID =@cid AND FeePartID =@pid 
END
ELSE
BEGIN
  UPDATE [tblFeePayment]
        SET   [EnrollID]= p.EnrollID ,[AdmissionNo] =p.AdmissionNo,[classfee]=p.FeeAmount
		 ,ClassID=p.ClassID ,[catdiscount]=p.CatDiscountAmount,[studiscount]=p.StuDiscountAmount ,FeePartID=p.FeePartID ,
		 [feeparticular]=p.particular
  FROM

  (SELECT DiSTINCT tblEnrollment.EnrollID,tblStudent.AdmissionNo, tblStuCategory.CategoryID,tblStuCategory.Category,tblClass.ClassID,tblClass.ClassName,tblFeeParts.FeePartID,tblFeeParts.particular, tblClassFee.FeeAmount,
tblCatDiscount.Discount AS CatDiscount, tblCatDiscount.is_Percent AS CatPercent,ISNULL((CASE WHEN tblCatDiscount.is_Percent = 1 THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100 ELSE tblCatDiscount.Discount END), 0) 
                         AS CatDiscountAmount,
tblStuDiscount.Discount AS StuDiscount, tblStuDiscount.is_percent AS StuPercent, ISNULL((CASE WHEN tblStuDiscount.is_percent = 1 THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount) 
                         / 100 ELSE tblStuDiscount.Discount END), 0) AS StuDiscountAmount
  FROM [tblEnrollment]
  INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
  INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
  INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
  INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
  INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
  INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
  LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID ) p
  WHERE p.ClassID =@cid AND p.FeePartID =@pid 
END
END
Posted
Updated 5 days ago
Comments
OriginalGriff 3-Sep-19 4:47am
   
"it did not work" is one of the error reports we get quite often, and it's always completely useless - it tells us nothing about your problem other than "I have a problem" and we knew that because you are asking a question!

What did it do that you didn't expect, or not do that you did?
When did it do it?
Are there any error messages?
What did you do to make it do that?
What have you tried to do to find out why?
What were the results?
What help do you need?

These are all questions we need an answer to - or we can't help you at all!
phil.o 3-Sep-19 4:49am
   
What is the problem? Of course an update will overwrite.
What result are you expecting instead?
nyt72 3-Sep-19 4:56am
   
The Problem is in the Update part, it over write all records instead of update only which matched with ClassID and FeePartID.

Means Over write those too which are not matching.
Santosh kumar Pithani 3-Sep-19 5:46am
   
Hello nyt72, your simply updating all records without maintaining proper relation on derived and updated table, try to use merge for these type of task.
Rate this:
Please Sign up or sign in to vote.

Solution 2

How your query is incorrect?
you had written where clause but it is on temp table only (that is inside sub query) here, you should write where clause on table tblFeePayment as well.

So try this

;WITH temp AS
(
	SELECT DISTINCT
		tblEnrollment.EnrollID
		,tblStudent.AdmissionNo
		,tblStuCategory.CategoryID
		,tblStuCategory.Category
		,tblClass.ClassID
		,tblClass.ClassName
		,tblFeeParts.FeePartID
		,tblFeeParts.particular
		,tblClassFee.FeeAmount
		,tblCatDiscount.Discount AS CatDiscount
		,tblCatDiscount.is_Percent AS CatPercent
		,ISNULL((CASE WHEN tblCatDiscount.is_Percent = 1 THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100 ELSE tblCatDiscount.Discount END), 0) AS CatDiscountAmount
		,tblStuDiscount.Discount AS StuDiscount
		,tblStuDiscount.is_percent AS StuPercent
		, ISNULL((CASE WHEN tblStuDiscount.is_percent = 1 THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount) / 100 ELSE tblStuDiscount.Discount END), 0) AS StuDiscountAmount
	FROM [tblEnrollment]
		INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
		INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
		INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
		INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
		INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
		INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
		LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID 
	WHERE 
		tblClass.ClassID =@cid AND tblFeeParts.FeePartID =@pid -----HERE IT IS KEY FILTER
)
UPDATE 
	FP
SET   
	[EnrollID]= p.EnrollID, [AdmissionNo] =p.AdmissionNo, [classfee]=p.FeeAmount
	,ClassID=p.ClassID, [catdiscount]=p.CatDiscountAmount, [studiscount]=p.StuDiscountAmount 
	,FeePartID=p.FeePartID,	[feeparticular]=p.particular
FROM
	[tblFeePayment] FP 
	INNER JOIN temp AS P -----HERE IT IS JOIN TO UPDATE PARTICULAR ROW IN tblFeePayment
	ON FP.ClassID = P.ClassID AND FP.FeePartID = P.FeePartID

Happy Coding!
:)
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

;WITH CTE
AS (
	SELECT DISTINCT tblEnrollment.EnrollID
		,tblStudent.AdmissionNo
		,tblStuCategory.CategoryID
		,tblStuCategory.Category
		,tblClass.ClassID
		,tblClass.ClassName
		,tblFeeParts.FeePartID
		,tblFeeParts.particular
		,tblClassFee.FeeAmount
		,tblCatDiscount.Discount AS CatDiscount
		,tblCatDiscount.is_Percent AS CatPercent
		,ISNULL((
				CASE 
					WHEN tblCatDiscount.is_Percent = 1
						THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100
					ELSE tblCatDiscount.Discount
					END
				), 0) AS CatDiscountAmount
		,tblStuDiscount.Discount AS StuDiscount
		,tblStuDiscount.is_percent AS StuPercent
		,ISNULL((
				CASE 
					WHEN tblStuDiscount.is_percent = 1
						THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount) / 100
					ELSE tblStuDiscount.Discount
					END
				), 0) AS StuDiscountAmount
	FROM [tblEnrollment]
	INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
	INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
	INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
	INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
	INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
	INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
	LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID
		                    AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID
	WHERE   tblClass.ClassID = @cid
		AND tblFeeParts.FeePartID = @pid
	)

UPDATE [tblFeePayment]
SET [EnrollID] = CTE.EnrollID
	,[AdmissionNo] = CTE.AdmissionNo
	,[classfee] = CTE.FeeAmount
	,ClassID = CTE.ClassID
	,[catdiscount] = CTE.CatDiscountAmount
	,[studiscount] = CTE.StuDiscountAmount
	,FeePartID = CTE.FeePartID
	,[feeparticular] = CTE.particular
FROM [tblFeePayment]
INNER JOIN CTE ON (
		    [tblFeePayment].ClassID = CTE.ClassID
		AND [tblFeePayment].FeePartID = CTE.FeePartID
		)
   
Comments
nyt72 3-Sep-19 9:04am
   
Thanks Santosh, i tried this but no luck, When I update, What it do is, duplicate top to records over other records in the same class.
Santosh kumar Pithani 4-Sep-19 0:18am
   
Try to remove duplicates by using Rownumber function on (ClassID,FeePartID) still your not clear then its better to post Dummy data along with expected output.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100