Last post Feb 13, 2020 03:09 PM by PatriceSc
Feb 13, 2020 02:16 AM|ahmedbarbary|LINK
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 #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 ,
insert into #Temp
DECLARE @MaxValue int = ( select Max(DocumentPartID) from #Temp)
DECLARE @Currentindex int =0
DECLARE @Rows [dbo].[Type_ValidationInPut];
while @Currentindex < @MaxValue
INSERT INTO @Rows
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
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
Feb 13, 2020 12:10 PM|PatriceSc|LINK
It really can't be done in one go? TOP could be replaced by selecting DocumentPartID between two values. You are inserting twice from more or less the same row(s). Generally speaking I always try to do things in one go rather by looping.
Also https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver15 could perhaps help to really
analyze what SQL Server does rather than trying to guess.
Feb 13, 2020 12:24 PM|ahmedbarbary|LINK
can i do another solution alternative to while loop
so please help me
Feb 13, 2020 01:12 PM|PatriceSc|LINK
You used a loop because? For now it seems to me that if :
select @newId,DocumentPartID , isnull(AffectedProduct,''), isnull(CompanyName,'')
where (CategoryID = 517884 or CategoryID = 1110481) and /*(DocumentPartID > @Currentindex) and*/ [Status] is null-- and replacementpart is null ???
select @newid,DocumentPartID, isnull(substring(ReplacementPart,0,70),''), isnull(ReplacementCompany,'') FROM #Temp
where /*(DocumentPartID > @Currentindex) and*/ [Status] is null and ReplacementPart is not null
is giving the data you want to insert (a bit worried about having two where criteria which doesn't seems to guarantee each row is processed one time) then you could use two INSERT statements as you are doing or you could also use :
INSERT INTO ...
UNION ALL SELECT ...
I believe you can do what you want with a single IN SERT statement or at worst two.
Feb 13, 2020 02:23 PM|ahmedbarbary|LINK
How to iterate within data
if i make select 5000 from temp
this will select only one time
I need to make them 5000 by 5000
so that i need to do that it main requirment
Feb 13, 2020 03:09 PM|PatriceSc|LINK
If you want to keep and enhance your current approach it seems to me you could skip using @rows to which you copy data to then copy them unchanged to the final table and deleting them from @rows.
If this to insert both SELECT inside a single transaction you could use an explicit transaction or the same UNION query shown above (just keep your full WHERE criteria)
Also I really don't like the TOP query without an ORDER BY (this is an old version, I would have thought this to be even not allowed ?)
Edit: else you could use ROW_NUMBER to generate a new batch id but I believe you are using an old SQL Server version which doesn't have this. Also ultimately using tools such as "show plan" allows SQL Server to tell you how he proceed and can give directions
for enhancing your query (do you have an index on DocumentPartId ?) rather than by guessing.