title: How to insert into SQL Server database in a batch?
body: <p>We execute SQL Server stored procedure <code>mySP</code> to insert into the database (say it will call the stored procedure about 300,000 times).</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[mySP]
      @ThePath VARCHAR(255),
      @TheID INT
  AS
      INSERT INTO myTbl (TheTime, ThePath, TheID)
      VALUES (GETDATE(), @ThePath, @TheID)
  </code></pre>
  
  <p>This is the code to execute stored procedure mySP:</p>
  
  <pre><code>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;
       }
  }
  </code></pre>
  
  <p>How can I do this in a batch of say 5000, then commit transaction ?</p>
  
  <p>Thank you</p>