How to write loop below with best practice performance ?

Question

4.00/5 (1 vote)

See more:

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

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 :

What I have tried:

SQL

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)

)

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 [dbo].PartsData (BatchID,RowNumber,GivenPartNumber,givenmanufacturer) 
			SELECT  @NewID ,0,GivenPartNumber,GivenManufacturer from  @Rows 
			

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

Posted 12-Feb-20 14:34pm

ahmed_sa

Updated 13-Feb-20 6:33am

v2

Add a Solution

3 solutions

Solution 2

One way of doing batch inserts:

SQL

DECLARE @Batchsize INT = 5000;
DECLARE @RowCount INT = @Batchsize;
DECLARE @LastID INT;

WHILE @RowCount = @Batchsize
BEGIN
    SET @LastID = (
            SELECT ISNULL(max(MyTableID), - 1)
            FROM MyTable
            );

    INSERT INTO MyTable (MyTableID,OtherColumns)
    SELECT  TOP (@Batchsize) 
            MyTableID
           ,OtherColumns
    FROM    TheSourceTable
    WHERE   MyTableID > @LastID
    ORDER BY MyTableID;

    SET @RowCount = @@ROWCOUNT;
END;

Adjust as necessary

Posted 13-Feb-20 3:07am

Jörgen Andersson

Solution 3

Going on from solution one, and utilising an ETL process you can use the import date from tasks in a Database

right click on the DB

and got tasks and then on the tasks choose import data. A MSFT wizard appears and follow it through to excel and the excel file can be mapped to table

see link below

Import and Export Data with the SQL Server Import and Export Wizard - SQL Server Integration Services (SSIS) | Microsoft Docs[^]

Posted 13-Feb-20 6:33am

MarcusCole6833

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-02-12T15:52:00

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 NCHAR(37)

SQL

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?

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
)

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.