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.