ahmedsa2018
Programmer
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 :
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 :
SQL:
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];
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 [ls30].[validation].[dbo].PartsData (BatchID,RowNumber,GivenPartNumber,givenmanufacturer)
SELECT @NewID ,0,GivenPartNumber,GivenManufacturer from @Rows
set @Currentindex = @Currentindex +5000
DELETE @Rows
end