how to insert Bulk data inside of a Datatable ,using stored procedure.

Question

Datatable has more than 45000 number of rows. i want to insert this data to ms sql server table using stored procedure. what is the best way to do this without lagging. using xmls or ?

Amit Mohanty · Answer

SQL Server provides a feature called SQL Bulk Copy, which is specifically designed for efficiently inserting large amounts of data into a table. It's much faster than individual INSERT statements. You can try this. // Assuming you have a DataTable named "dataTable" with your data using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { //assign Destination table name bulkCopy.DestinationTableName = "YourTableName"; //map your datatable column name with db table column name sqlBulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1"); sqlBulkCopy.ColumnMappings.Add("SourceColumn2", "DestinationColumn2"); // Set other options like BatchSize, BulkCopyTimeout, etc. // bulkCopy.BatchSize = 1000; // bulkCopy.BulkCopyTimeout = 600; bulkCopy.WriteToServer(dataTable); } }

Manjit Vaghadiya · Answer

Hi, Lahiru Kalutotage You can insert/update data using the data table to sql server using the storeprocedure by using this way. Store Procedure CREATE PROCEDURE SP_Bulk_Insert_Update_EmployeeDetails @EmployeeDetails EmployeeType READONLY AS BEGIN SET NOCOUNT ON; MERGE INTO Employee E1 USING @EmployeeDetails E2 ON E1.Id=E2.Id WHEN MATCHED THEN UPDATE SET E1.Name = E2.Name, E1.Address = E2.Address, E1.Mobile = E2.Mobile WHEN NOT MATCHED THEN INSERT VALUES(E2.Id, E2.Name, E2.Address, E2.Mobile); END from C# backend code you need to pass using (SqlCommand cmd = new SqlCommand("SP_Bulk_Insert_Update_EmployeeDetails", connection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmployeeDetails", EmployeeDataTable); connection.Open(); cmd.ExecuteNonQuery(); }

Ajay Kumar · Answer

Inserting a large number of rows into a SQL Server table can be challenging, as you'll want to ensure the process is efficient and doesn't cause performance issues. There are several methods you can consider, including using XML, BULK INSERT, or SqlBulkCopy. The best approach depends on your specific requirements and constraints. Here's an overview of these methods: XML : You can serialize your DataTable into XML and pass it to a stored procedure as a parameter. The stored procedure can then parse the XML and insert the data into the table. This can work well for smaller datasets, but it may not be the most efficient method for very large datasets, as XML parsing can be resource-intensive. BULK INSERT : SQL Server provides a BULK INSERT statement that allows you to efficiently load data from a flat file into a table. You can write your DataTable to a CSV or other flat file format and then use BULK INSERT to load it into your target table. This method can be highly efficient for large datasets. BULK INSERT YourTable FROM 'C:\YourData.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '
', BATCHSIZE = 1000 ) 3. SqlBulkCopy : If you're working with .NET (C# or VB.NET), you can use the SqlBulkCopy class to efficiently insert a large number of rows into a SQL Server table. This method is highly efficient and suitable for very large datasets. It leverages bulk insert operations and is often the preferred method for high-performance data insertion. using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "YourTable"; bulkCopy.WriteToServer(dataTable); } }