再次执行存储过程时会附加数据 [英] data gets appended when executing the stored procedure again

问题描述

我在执行SP时收到重复数据。请帮帮我。

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

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

推荐答案

您可以使用

You can check if the record already exists in the table using

IF NOT EXISTS(SELCT * FROM table_name (applicable where clause))

只有当表没有相同的记录时才会插入记录（或者在任何情况下，没有记录。取决于你使用的where子句。）



我认为这会有所帮助。

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.

这篇关于再次执行存储过程时会附加数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！