Skip to main content

Batch insert script for large table- Optimize Large SQL Server Insert by Using Batches

Batch insert script for large table- Optimize Large SQL Server Insert  by Using Batches

Here the scenario is to perform large insert of millions of records on SQL Server tables  in an optimized way.

Issue: 
Sometimes you may have to performs large insert, like bringing millions of data from the application table to a datawarehouse tables.

Challenge: 
If we perform this as a usual single insert , it may end up taking hours\days to complete ,and may lead to locks/blocks and fill-up the logs.
  
To avoid large log growths and minimize locks on tables,  we can follow a better optimized way of batchwise insert.

Implementation: 
We can do it in many ways. Here is a sample script which bring data batchwise from application table to its corresponding DW table.
Here the insert is limited to 100000 records per batch. Once it insert 100000 rows it take next 100000 records and insert. It also check if data is already exist in destination table. 

Here the source and destination tables are in different db servers. So a linked server approach is used to connect to the source db.

Sample script:

declare @Count int
set @Count = 1
while @Count > 0
   begin
INSERT  [Audit] ( --Destination DW Table
AuditID,
ModifiedDate,
ModifiedBy,
CreatedDate,
CreatedBy,
DWIsDeleted,  
DWStartDate, 
DWEndDate
)  
SELECT top (100000)      
AuditID,
ModifiedDate,
ModifiedBy,
CreatedDate,
CreatedBy,
0 AS DWIsDeleted,  
[CreatedDate] AS [DWStartDate], 
NULL AS DWEndDate
FROM    OPENQUERY(
[AppDB], --Linked server to access application db
'SELECT
AuditID,
ModifiedDate,
ModifiedBy,
CreatedDate,
CreatedBy
FROM [stgdb].[dbo].[Audit] AS x WITH (NOLOCK)' --source db
)AS x  
WHERE NOT EXISTS ( --data not exist in destination db check
SELECT 1  
FROM [Audit] AS dw WITH ( NOLOCK )  
WHERE dw.DWEndDate IS NULL  
AND dw.[AuditID] = x.[AuditID]
)
set @Count = @@ROWCOUNT
end
GO


Conclusion:
This is one of the way for doing batchwise insert for large tables. There are other ways too to accomplish the same. Which to choose would depends on each scenarios.

Comments

Popular posts from this blog

  Microsoft Power BI- Solve Problem  "Map and Filled Map Visuals aren't Enabled"   When you try to use Map or Filled Map from Visuals for the 1st time , you may get the following warning : "Map and Filled Map Visuals are disabled". This means you need to go to Options and have to enable these Visuals. Go to File ->Options and Settings-> Options -> Security-> Enable Map and Filled Map Visuals. (After this, in some cases you may need to close and reopen Power BI desktop) Now Go to Add Visual Then double click on Map or Filled Map. After that, you will be able to add Location, Legends etc. without any issues.

Difference between Clustered Index Scan and Index Scan in SQL Server

1.Clustered Index Scan ✅ What it is: A Clustered Index Scan means SQL Server is scanning every row of the entire table,  essentially performing a "table scan" because the clustered index is the table itself.  📌 When it happens: No WHERE clause or very broad filter (low selectivity). Join/filter columns aren’t indexed selectively. Query needs most or all columns (covering queries). 🧠 Performance Insight: Can be expensive for large tables. Usually appears when SQL Server can’t seek and needs to read all data. Example:    Table: CREATE TABLE dbo.Sales (     SaleID INT NOT NULL IDENTITY,     SaleDate DATE NOT NULL,     Quantity INT NOT NULL, PRIMARY KEY (SaleID) ) Query: SELECT * FROM dbo.Sales WHERE  SaleDate  BETWEEN '2025-02-04' AND '2025-02-10' 2. Index Scan (Non-Clustered) ✅ What it is: A Non-Clustered Index Scan scans all rows in a specific non-clustered index( not the full table) 📌 When it happens: The query filters on a ...

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.