The first thing that stood out to me in this was a poor choice in a variable declaration.
I see NO valid reason for this as @NewID will always be the same size; you would be better off with using
DECLARE @NewID nVARCHAR(4000) =newID()
Naturally to try and help you out I copied/pasted your code into SSMS and started with reformatting.
Your #temp table itself is asking for memory loads. Did you know there is a line length limit in SQL Server of 8060? Once you hit cross that threshold it takes multiple pages of memory to hold each row. As it sits right now your table is well over 32,0000 bytes.
Do you really need to define every non-integer as 4000 characters long?
create table #Temp(
DocumentPartID int identity(1,1),
ReplacementPart VARCHAR(4000) ,
ReplacementCompany VARCHAR(4000) ,
Category VARCHAR(4000) ,
DocumentID int null,
CompanyID VARCHAR(4000) null,
PartID int null,
ReplacementPartID int null,
CategoryID int null,
[Status] VARCHAR(4000) null
Once I get it all formatted nicely into something readable, I notice a few more things:
1. Either there is some code missing, or your first INSERT statement will never fire; as CategoryID is never populated.
2. The first and last items in your loop both delete @Rows
. Should only need to be done once within the loop.
If this routine is going to be used often; I would recommend that you create an SSIS (ETL: Extract-Transform-Load) package for it which will have a much lower impact on your SQL Server resources and operate much faster, and can easily process billions of records.