Click here to Skip to main content
15,889,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,

i am getting duplicate data when executing the SP. Please help me with that.

SQL
CREATE PROCEDURE [dbo].[BMCOveragesuUpdate]
AS
BEGIN

	CREATE TABLE #BMCOveragesuUpdate ( AccountID INT, StartDate DATETIME, EndDate DATETIME, TransactionCount DECIMAL(15,3) )
	
	CREATE TABLE #AccountUsers
	(
		[new_accountid] [int] NOT NULL,
		[name] [nvarchar](400) NULL,
		[new_companyname] [nvarchar](200) NULL,
		[new_contractid] [nvarchar](100) NULL,
		[new_contractstartdate] [date] NULL,
		[new_contractenddate] [date] NULL,
		[new_vlenrollmentnumber] [nvarchar](100) NULL,
		[new_vlenrollmentstartdate] [date] NULL,
		[new_vlenrollmentenddate] [date] NULL,
		[new_ownertypename] [nvarchar](4000) NULL,
		[new_ownertype] [int] NULL,
		[new_email] [nvarchar](400) NULL,
		[new_unitspurchased] [nvarchar](200) NULL
		
	)

	INSERT INTO #AccountUsers (new_accountid, name, new_companyname, new_contractid, new_contractstartdate, 
			new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate, 
			new_ownertypename, new_ownertype, new_email, new_unitspurchased)			
	SELECT new_accountid, name, new_companyname, new_contractid, new_contractstartdate, 
		new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate, 
		new_ownertypename, new_ownertype, new_email, new_unitspurchased --, TransactionCount, YearUsage
	--INTO #AccountUsers

	FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_accountuser FAU WITH (NOLOCK)
	INNER JOIN BingMapsPlatform_MSCRM.dbo.Filterednew_new_accountuser_account FAUA  WITH (NOLOCK)
	ON FAU.new_accountuserid = FAUA.new_accountuserid
	INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
	ON FAUA.accountid = FA.accountid
	WHERE FA.new_ownertype IN (2,7,8,10,11)


	DECLARE @new_accountid int , @Startdate DATETIME, @EndDate DATETIME
	DECLARE @TempEndDate DATETIME
	
	DECLARE account_cursor CURSOR FOR
	SELECT new_accountid, new_contractstartdate,  new_contractenddate
	FROM #AccountUsers
	ORDER BY new_accountid

	OPEN account_cursor;

	FETCH NEXT FROM account_cursor
	INTO @new_accountid, @startdate, @enddate
	


	DECLARE @CurrentYear DATETIME

	
	-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
	WHILE @@FETCH_STATUS = 0
	BEGIN
	   
		SET @CurrentYear = @startdate

		WHILE @CurrentYear < @enddate
		BEGIN
			SET @TempEndDate = DATEADD(YYYY, DATEDIFF(yyyy,0,@CurrentYear)+ 1, 0)
			--SELECT @new_accountid, @startdate, @enddate, @CurrentYear, @TempEndDate
			INSERT INTO #BMCOveragesuUpdate
			EXEC BingMapsPlatform_Staging.dbo.GetAccountBillableUsage @new_accountid, @CurrentYear, @TempEndDate
			
		
			SET @CurrentYear = @TempEndDate
		END
		
		
		IF EXISTS(select 1 from #BMCOveragesuUpdate)
		BEGIN
				
			INSERT INTO BMCOverages (new_accountid, name, new_companyname, new_contractid, new_contractstartdate, new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate, new_ownertypename, new_ownertype, new_email, new_unitspurchased, TransactionCount, StartDate, EndDate, LastUpdated)
			SELECT AU.new_accountid,AU.name , AU.new_companyname, AU.new_contractid, AU.new_contractstartdate, 
			AU.new_contractenddate, AU.new_vlenrollmentnumber,AU.new_vlenrollmentstartdate, AU.new_vlenrollmentenddate, 
			AU.new_ownertypename, AU.new_ownertype, new_email, AU.new_unitspurchased,  BMC.TransactionCount, BMC.StartDate, BMC.EndDate, GETDATE()
			FROM #BMCOveragesuUpdate BMC
			INNER JOIN #AccountUsers AU ON BMC.AccountID = AU.new_accountid
			WHERE BMC.TransactionCount is not null
			
			
		END
		
		TRUNCATE TABLE #BMCOveragesuUpdate
		

		-- This is executed as long as the previous fetch succeeds.
		FETCH NEXT FROM account_cursor
		INTO @new_accountid, @startdate, @enddate
	END

	CLOSE account_cursor;
	DEALLOCATE account_cursor;

	drop table #AccountUsers
	
	

END
GO
Posted
Comments
CHill60 20-Dec-12 9:48am    
The title of your question would suggest that this only happens when you call the SP again and not when you call it the first time. If this is so then you need to add something the WHERE clause of the INSERT INTO BMCOverages ... bit to not insert a row if it already exists.

1 solution

You can check if the record already exists in the table using
C#
IF NOT EXISTS(SELCT * FROM table_name (applicable where clause))

The record will be inserted only if the table does not have the same record (or in any case, no records. Depends on the where clause you use.)

I think this will help.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900