How to insert into SQL Server database in a batch?

Question

We execute SQL Server stored procedure mySP to insert into the database (say it will call the stored procedure about 300,000 times).

ALTER PROCEDURE [dbo].[mySP]
    @ThePath VARCHAR(255),
    @TheID INT
AS
    INSERT INTO myTbl (TheTime, ThePath, TheID)
    VALUES (GETDATE(), @ThePath, @TheID)

This is the code to execute stored procedure mySP:

using (SqlConnection con = new SqlConnection(connectionString))
{
:
     foreach (.....)  //about 300,000 times
     {
         SqlCommand MyCmd = new SqlCommand("mySP", con);
         MyCmd.CommandType = CommandType.StoredProcedure;
         MyCmd.Parameters.Add(new SqlParameter("ThePath", sMyPath));
         MyCmd.Parameters.Add(new SqlParameter("TheID", sMyID));

         iRow = MyCmd.ExecuteNonQuery();

         MyCmd.Dispose();
         MyCmd = null;
     }
}

How can I do this in a batch of say 5000, then commit transaction ?

Thank you

why don't you do it over the SP? do a loop inside the SP with the batch number you want. — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jan 30, 2019 at 21:46
Hi, I need to do the loop in the application to get the information that I need to insert into the database. — faujong
– faujong, Commented Jan 30, 2019 at 21:54
Batching means writing a multi-statement SQL. Looping isn't needed. Batching multiple inserts doesn't make them faster either, it saves the network roundtrip. Bulk inserts on the other hand do improve performance by using minimal logging. You can bulk insert data with the bcp command, the BULK INSERT` statement or the SqlBulkCopy class — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 31, 2019 at 10:41
SqlBulkCopy can accept a DataTabel or DataReader as source, which means you can feed it the results of other database queries — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 31, 2019 at 10:41

Thailo · Accepted Answer · 2019-01-31 10:36:05Z

When inserting thousands of rows at a time you are better off using something like .Net's own SqlBulkCopy, which basically means you have to populate a DataTable first and then use SqlBulkCopy to write it to the database. See https://sqlbulkcopy-tutorial.net/ for good examples. A little advice is to toy around with the BatchSize property; generally leaving it as-is is good enough. Don't forget to use a transaction as well.

Example:

using (SqlConnection connection = new SqlConnection("server=local);database=MyDatabase;integrated security=SSPI"))
{
    connection.Open();

    DirectoryInfo directory = new DirectoryInfo(@"C:\Temp\");

    var files = directory.GetFiles("*.dat");

    foreach (var file in files)
    {
        if (file.Length > 0)
        {
            string fileName = Path.GetFileNameWithoutExtension(file.FullName);

            SqlCommand cmd = new SqlCommand("dbo.uspStagingGetTableStructure", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tableName", fileName);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                }
            }

            //
            // Prepare Bulkcopy target (datatable)
            //

            string target = "MyBulkCopyTarget";

            DataTable dataTable = new DataTable(target);
            SqlBulkCopy bulkcopy = new SqlBulkCopy(connection);
            bulkcopy.DestinationTableName = target;
            bulkcopy.BulkCopyTimeout = 600;

            //
            // Map columns source -> target
            //

            //foreach (var dataField in dataFields)
            //{
            //    if (dataField.sqlDbType == SqlDbType.Int)
            //        dataTable.Columns.Add(dataField.fieldName, typeof(int));
            //    else
            //        dataTable.Columns.Add(dataField.fieldName, typeof(string));
            //    bulkcopy.ColumnMappings.Add(dataField.fieldName, dataField.fieldName);
            //}

            //
            // Populate Bulkcopy target (datatable)
            //

            string line = string.Empty;
            using (StreamReader reader = file.OpenText())
            {
                while ((line = reader.ReadLine()) != null)
                {
                    DataRow row = dataTable.NewRow();

                    //
                    // Use proper data types
                    //

                    //foreach (var dataField in dataFields)
                    //{
                    //    if (dataField.sqlDbType == SqlDbType.Int)
                    //        row[dataField.fieldName] = Convert.ToInt32(line.Substring(dataField.fieldStartPos, dataField.fieldLength));
                    //    else
                    //        row[dataField.fieldName] = line.Substring(dataField.fieldStartPos, dataField.fieldLength);
                    //}

                    dataTable.Rows.Add(row);
                }
            }

            //
            // Push datatable to server
            //

            bulkcopy.WriteToServer(dataTable);
        }
    }
}
}
}

Hi, in my case, since I do a foreach loop in the application to get the input data, how do I do the sqlbulkCopy ? Instead of executing the stored procedure mySP inside the loop, do I want to populate the DataTable inside the Loop, then sqlbulkCopy the dataTable ?

marc_s · Accepted Answer · 2019-02-10 12:54:11Z

0

Create type in SQL Server and pass table as parameter. Please look below

How to pass table value parameters to stored procedure from .net code

https://www.c-sharpcorner.com/UploadFile/ff2f08/table-value-parameter-use-with-C-Sharp/

Once, you pass the table variable to the stored procedure, you can insert data in one insert select statement.

edited Feb 10, 2019 at 12:54

marc_s

760k185 gold badges1.4k silver badges1.5k bronze badges

answered Jan 31, 2019 at 2:30

Maddy

9273 gold badges10 silver badges25 bronze badges

2 Comments

faujong Over a year ago

Hi Maddy/Marc_s..with table value parameter, do we insert a number of records then commit (like the SqlBulkCopy) ? My concern is I don't want to fill up the database Transaction Log, so I would like to insert say 5000 records, then commit, then the next 5000 records then commit, etc.

Maddy Over a year ago

Yes we can insert n numbers of record as per your need. Create loop for 5000 rows in data table and call store proc.

Collectives™ on Stack Overflow

How to insert into SQL Server database in a batch?

2 Answers 2

4 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Linked

Related