How to replace while loop with another best practice ?

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
yes I have service working if i give it more than 5000 it hangs and fail
so that i need to pass it 5000 row by 5000
so can you help me please
0
Joginder Banger6y ago
My question is why are you need to execute 5000 for every iteration, is there any specfic reason. Becuase if you are using loop might be performance is slow.
0
ahmed elbarbary6y ago
I need to execute 5000 for every iteration
0
ahmed elbarbary6y ago
what i use if limit around 20 to 30 k instead of while loop
0
Joginder Banger6y ago
if you have data row limit around 20 to 30 k, then no need to while loop.
0
ahmed elbarbary6y ago
can i use any thing more faster from while loop
0
Joginder Banger6y ago
Hi,
I assume that in excel sheet have 25,000 records. So I heared about the "Memory-Optimized Tables" this table is 10 times faster than #temptable. Becuase #temptable is working on temp db (Hard disk) and Memory optimized tables run in memory. You know that which is faster.

I think don't be used while loop when inserted the data. You need just remove the data on temptable. why are you skip inserted data.

Thanks
Joginder
0
ahmed elbarbary6y ago
I have excel file sheet have may be 20000 rows
i upload them on temp table
then i loop to them increase 5000 by 5000
until finish 20000 row
so that i use while loop
0
Joginder Banger6y ago
Hi,

Before going to proceed, I need some information more regarding your requirement. why are you using while loop I can't understand. If am ask a wrong question, please correct me.

Thanks
Joginder Singh
0