I have a stored procedure that attempts to INSERT @BatchSize number of records at a time into a table. Currently, I have @BatchSize set to load 50,000 at a time. The table I am inserting from has a little over 67,000 records. 

When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.

Below is my code:

begin try

	--error catching variables:
	declare @Error_NumberLocal int
			 ,@Error_MessageLocal varchar(4000)
			 ,@Error_SeverityLocal int
			 ,@Error_StateLocal int
			 ,@Error_ProcedureLocal varchar(200)
			 ,@Error_LineLocal int
			 ,@User_NameLocal varchar(200)
			 ,@Server_NameLocal varchar(200)
			 ,@Database_NameLocal varchar(200)
			 ,@Time_StampLocal datetime

	declare @created_on datetime, @created_by nchar, @BatchSize int

	select @created_on = getdate(), @created_by = SYSTEM_USER, @BatchSize = 50000

	while 1=1
	begin

		Insert etlrpt
		(
			etr_transaction_date
			,etr_transaction_id
			,etr_transaction_type
			,etr_isbn
			,etr_imprint
			,etr_title
			,etr_author
			,etr_transaction_quantity
			,etr_transaction_quantity_abs
			,etr_list_currency
			,etr_list_price
			,etr_list_with_tax
			,etr_list_wo_tax
			,etr_publisher_price_currency
			,etr_publisher_price
			,etr_purchase_price_currency
			,etr_purchase_price
			,etr_bill_to_country
			,etr_bill_to_state
			,etr_bill_to_zip
			,etr_tax_rate
			,etr_tax_amount
			,etr_publisher_revenue_percent
			,etr_publisher_revenue_amount
			,etr_publisher_revenue_plus_tax
			,etr_earnings_currency
			,etr_earnings_amount
			,etr_currency_conversion_rate
			,etr_created_on
			,etr_created_by
			)

		select top(@BatchSize)
	
			cast([Transaction Date] as datetime) as [Transaction Date]
			,cast(Id as nvarchar(40)) as [Id]
			,cast([Type] as nvarchar(20)) as [Type]
			,cast([Primary ISBN] as nvarchar(20)) as [Primary ISBN]
			,cast([Imprint Name] as nvarchar(100)) as [Imprint Name]
			,cast(Title as nvarchar(80)) as Title
			,cast(Author as nvarchar(80)) as Author
			,cast(Qty as numeric(15,2)) as QTY
			,cast(Qty as numeric(15,2)) as QTY_ABS
			,cast([Original List Price Currency] as nvarchar(10)) as [Original List Price Currency]
			,cast([Original List Price] as numeric(15,2)) as [Original List Price]
			,cast([List Price (tax inclusive)] as numeric(15,2)) as [List Price (tax inclusive)]
			,cast([List Price (tax exclusive)] as numeric(15,2)) as [List Price [tax exclusive)]
			,cast([Purchase Price Currency] as nvarchar(10)) as [Purchase Price Currency Publisher]
			,cast([Purchase Price] as numeric(15,2)) as [Purchase Price Publisher]
			,cast([Purchase Price Currency] as nvarchar(10)) as [Purchase Price Currency Purchase]
			,cast([Purchase Price] as numeric(15,2)) as [Purchase Price Purchase]
			,cast([Country of Sale] as nvarchar(3)) as [Country of Sale]
			,cast([State/Province/Region] as nvarchar(2)) as [State/Province/Region]
			,cast([Postal Code] as nvarchar(10)) as [Postal Code]
			,cast(substring([Tax Rate],1,Len([Tax Rate])-3) as numeric(8,5)) as [Tax Rate]
			,convert(float,[Tax Amount]) as [Tax Amount]
			,cast([Publisher Revenue %] as numeric(7,2)) as [Publisher Revenue %]
			,cast([Publisher Revenue] as numeric(15,2)) as [Publisher Revenue]
			,cast([Publisher Revenue Plus Tax] as numeric(15,2)) as [Publisher Revenue Plus Tax]
			,cast([Earnings Currency] as nvarchar(10)) as [Earnings Currency]
			,cast([Earnings Amount] as numeric(15,2)) as [Earnings Amount]
			,cast([Currency Conversion Rate] as numeric(8,5)) as [Currency Conversion Rate]
			,@created_on as created_on
			,@created_by as created_by

		from dbo.v_Google src
		inner join dbo.ETailer_Matrix ex
					on src.NameOfFile = ex.NameOfFile
		where not exists(	select 1
							from dbo.etlrpt dest
							where  etr_transaction_id	= src.Id
							and etr_isbn				= src.[Primary isbn]
							and etr_transaction_type	= src.[Type]
							and etr_file_name			= src.NameOfFile

						)

		if @@ROWCOUNT < @BatchSize BREAK

	end

end try
begin catch
	
		select	
		@Error_NumberLocal		= error_number()
		,@Error_MessageLocal	= error_message()
		,@Error_SeverityLocal	= error_severity()
		,@Error_StateLocal		= error_state()
		,@Error_LineLocal		= error_line()
		,@Error_ProcedureLocal	= error_procedure()
		,@User_NameLocal		= SUSER_SNAME()
		,@Server_NameLocal		= @@SERVERNAME
		,@Database_NameLocal	= DB_Name()
		,@Time_StampLocal		= GETDATE()

	exec dbo.ErrorLogInsert
		@Error_Number			= @Error_NumberLocal		
		, @Error_Message		= @Error_MessageLocal	
		, @Error_Severity		= @Error_SeverityLocal	
		, @Error_State			= @Error_StateLocal		
		, @Error_Procedure		= @Error_ProcedureLocal		
		, @Error_Line			= @Error_LineLocal	
		, @User_Name			= @User_NameLocal		
		, @Server_Name			= @Server_NameLocal		
		, @Database_Name		= @Database_NameLocal	
		, @Time_Stamp			= @Time_StampLocal		

end catch

What is the problem with the looping structure that would cause this issue?

Thank you for your help.

cdun2

I believe the issue is with the insert statement.  You are doing a TOP statement, but I don't see how you are iterating through the data set per batch.

OK, I see what you are doing now.  You are using the NOT EXIST statement to identify what rows exist in the source and not in the target.  You loop indefinitely until no rows are inserted into the table.  Let me think about this some more.

By any chance, is this running in a controlled environment?  I am just wondering if there are records constantly being added to the source table preventing the BREAK condition.

When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.

Could you clarify what you mean by NOCOUNT left off?  Also, how can the number of records surpass the number of records in the source table if only 50,000 records are inserted into the target?

Also, your join in the EXIST statement uses column etr_file_name.  However, I don't see it in the insert statement of the target table.  Something seems wrong there.  Is there another process that keeps that column between the two tables in sync?