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

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);
    }
}

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();

}

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:

1. 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.

2. 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 = '\n',
   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);
    }
}