---
title: "What is the best and fast way to insert 2 million rows of data into SQL Server?"  
description: "What is the best and fast way to insert 2 million rows of data into SQL Server?"  
author: "Erick Wilsom"  
published: 2022-10-21  
updated: 2023-11-27  
canonical: https://www.mindstick.com/forum/157166/what-is-the-best-and-fast-way-to-insert-2-million-rows-of-data-into-sql-server  
category: ".net"  
tags: [".net", "asp.net"]  
reading_time: 2 minutes  

---

# What is the best and fast way to insert 2 million rows of data into SQL Server?

What is the best and [fast](https://www.mindstick.com/articles/12289/build-your-store-the-fast-way-with-magento-2-frontend-builder) way to [insert](https://www.mindstick.com/blog/173/executing-insert-delete-or-update-query-in-sqlserver-using-ado-dot-net) 2 million rows of [data](https://www.mindstick.com/articles/13050/salesforce-aiming-to-dominate-predictive-analytics-with-data-science) into [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server)?

## Replies

### Reply by Aryan Kumar

\
Inserting a large number of rows into a [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) database efficiently involves optimizing the process to minimize the impact on performance. Here are some best practices for inserting a large amount of data:

## Use Bulk Insert:

- The most efficient way to insert a large number of rows is to use the **BULK INSERT** statement. This is a SQL command specifically designed for high-performance bulk data-loading scenarios.

## Consider Batch Inserts:

- Break down the data into smaller batches and perform multiple smaller inserts instead of a single large one. This helps to manage transaction log growth and reduces contention.

## Disable Indexes and Constraints:

- Temporarily disable non-clustered indexes and constraints before the insert and re-enable them afterward. This can significantly speed up the insertion process.

## Use the TABLOCK Hint:

- Adding the **TABLOCK** hint to your **INSERT** statement can improve performance by reducing contention.

## Consider Using Table Partitioning:

- If applicable, consider using table partitioning. This can enhance the performance of large data inserts, especially if the table is partitioned appropriately.

## Adjust Database Recovery Model:

- If you are working with a production database, consider changing the database recovery model to **SIMPLE** during the data insertion and reverting it afterward. This reduces the amount of transaction log activity.

## Use SQL Server Import/Export Wizard:

- SQL Server Management Studio (SSMS) provides an Import/Export Wizard that can help streamline the process of bulk data insertion. This wizard generates efficient bulk insert statements.

## Optimize Disk I/O:

- Ensure that your disk subsystem is optimized for high I/O performance to handle the large amount of data being inserted.

Remember to thoroughly test any changes or optimizations in a non-production environment before applying them to a production database. Additionally, consider working with your database administrator to ensure that these actions align with your organization's policies and procedures.


---

Original Source: https://www.mindstick.com/forum/157166/what-is-the-best-and-fast-way-to-insert-2-million-rows-of-data-into-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.