How to replace while loop with another best practice ?

Question

I work on SQL server 2012

 

I have temp table get data from excel and based on data exist on excel i insert on table inside loop

 

temp table always have big amount of data may be at least 5000 or 10000 or 15000 or more

 

I need every iteration increased by 5000 rows insert from temp table

 

so that i need best solutions for that according to speed and memory like that

 

and if there are any thing not correct as logic please tell me

 

my Query as below :

 

--create table [dbo].PartsData
Create Table [dbo].PartsData
(
    BatchID nvarchar(50) primary key,
    RowNumber int,
    GivenPartNumber nvarchar(50),
    GivenManufacturer nvarchar(100)

)

--select * from [dbo].PartsData
Create Table [dbo].[Type_ValidationInPut]
(
    RowNumber int,
    GivenPartNumber nvarchar(50),
    GivenManufacturer nvarchar(100)

)
--drop table #Temp
create table #Temp(
    DocumentPartID int identity(1,1),
    CompanyName VARCHAR(4000),
    [AffectedProduct] NVARCHAR(4000),
    [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 ,


)



insert into #Temp
(
CompanyName ,
[AffectedProduct],
[ReplacementPart],
[ReplacementCompany],
[Category]
)
values
('Nokia','RF1550','RF1550','HTS','HTS'),
('IPHONE','TF1545','TF1545','Corning Incorporated','HTS2')



DECLARE @MaxValue int = ( select Max(DocumentPartID) from #Temp)
DECLARE @Currentindex int =0
--DECLARE @Rows  [dbo].[Type_ValidationInPut];
DECLARE @Rows  [dbo].[Type_ValidationInPut];
           while @Currentindex < @MaxValue
            begin


              DELETE @Rows
              INSERT  INTO @Rows
                        (
                        RowNumber ,
                 GivenPartNumber ,
                  GivenManufacturer
                )


            select TOP 5000 DocumentPartID , isnull(AffectedProduct,''), isnull(CompanyName,'') FROM #Temp where

            (CategoryID = 517884 or CategoryID = 1110481)  and (DocumentPartID > @Currentindex) and [Status] is null

                INSERT  INTO @Rows
                        (
                 RowNumber ,
                 GivenPartNumber ,
                  GivenManufacturer
                )


            select TOP 5000 DocumentPartID, isnull(substring(ReplacementPart,0,70),''), isnull(ReplacementCompany,'') FROM #Temp where
            (DocumentPartID > @Currentindex) and  [Status] is null and ReplacementPart is not null


            DECLARE @NewID nVARCHAR(4000) =newID()
            insert into [dbo].PartsData (BatchID,RowNumber,GivenPartNumber,givenmanufacturer)
            SELECT  @NewID ,0,GivenPartNumber,GivenManufacturer from  @Rows



            set @Currentindex = @Currentindex +5000
            DELETE @Rows
             end