title: How to change slow parametrized inserts into fast bulk copy (even from memory)
body: <p>I had someting like this in my code (.Net 2.0, MS SQL)</p>
  
  <pre><code>SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial
  Catalog=DataBase;Integrated Security=True");
    connection.Open();
  
    SqlCommand cmdInsert = connection.CreateCommand();
    SqlTransaction sqlTran = connection.BeginTransaction();
    cmdInsert.Transaction = sqlTran;
  
    cmdInsert.CommandText =
       @"INSERT INTO MyDestinationTable" +
        "(Year, Month, Day, Hour,  ...) " +
        "VALUES " +
        "(@Year, @Month, @Day, @Hour, ...) ";
  
    cmdInsert.Parameters.Add("@Year", SqlDbType.SmallInt);
    cmdInsert.Parameters.Add("@Month", SqlDbType.TinyInt);
    cmdInsert.Parameters.Add("@Day", SqlDbType.TinyInt);
    // more fields here
    cmdInsert.Prepare();
  
    Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
  
    StreamReader reader = new StreamReader(stream);
    char[] delimeter = new char[] {' '};
    String[] records;
    while (!reader.EndOfStream)
    {
      records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);
  
      cmdInsert.Parameters["@Year"].Value = Int32.Parse(records[0].Substring(0, 4));
      cmdInsert.Parameters["@Month"].Value = Int32.Parse(records[0].Substring(5, 2));
      cmdInsert.Parameters["@Day"].Value = Int32.Parse(records[0].Substring(8, 2));
      // more here complicated stuff here
      cmdInsert.ExecuteNonQuery()
    }
    sqlTran.Commit();
    connection.Close();
  </code></pre>
  
  <p>With <em>cmdInsert.ExecuteNonQuery()</em> commented out this code executes in less than 2 sec. With SQL execution it takes 1m 20 sec. There are around 0.5 milion records. Table is emptied before. SSIS data flow task of similar functionality takes around 20 sec.</p>
  
  <ul>
  <li>Bulk Insert <strong>was</strong> not an option (see below). I did some fancy stuff during this import.</li>
  <li>My test machine is Core 2 Duo with 2 GB RAM. </li>
  <li>When looking in Task Manager CPU was not fully untilized. IO seemed also not to be fully utilized.</li>
  <li>Schema is simple like hell: one table with AutoInt as primary index and less than 10 ints, tiny ints and chars(10).</li>
  </ul>
  
  <p>After some answers here I found that it is possible to execute <strong>bulk copy from memory</strong>! I was refusing to use bulk copy beacuse I thought it has to be done from file...</p>
  
  <p>Now I use this and it takes aroud 20 sec (like SSIS task)</p>
  
  <pre><code>  DataTable dataTable = new DataTable();
  
    dataTable.Columns.Add(new DataColumn("ixMyIndex", System.Type.GetType("System.Int32")));   
    dataTable.Columns.Add(new DataColumn("Year", System.Type.GetType("System.Int32")));   
    dataTable.Columns.Add(new DataColumn("Month", System.Type.GetType("System.Int32")));
    dataTable.Columns.Add(new DataColumn("Day", System.Type.GetType("System.Int32")));
   // ... and more to go
  
    DataRow dataRow;
    object[] objectRow = new object[dataTable.Columns.Count];
  
    Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
  
    StreamReader reader = new StreamReader(stream);
    char[] delimeter = new char[] { ' ' };
    String[] records;
    int recordCount = 0;
    while (!reader.EndOfStream)
    {
      records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);
  
      dataRow = dataTable.NewRow();
      objectRow[0] = null; 
      objectRow[1] = Int32.Parse(records[0].Substring(0, 4));
      objectRow[2] = Int32.Parse(records[0].Substring(5, 2));
      objectRow[3] = Int32.Parse(records[0].Substring(8, 2));
      // my fancy stuf goes here
  
      dataRow.ItemArray = objectRow;         
      dataTable.Rows.Add(dataRow);
  
      recordCount++;
    }
  
    SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null);
    bulkTask.DestinationTableName = "MyDestinationTable"; 
    bulkTask.BatchSize = dataTable.Rows.Count;
    bulkTask.WriteToServer(dataTable);
    bulkTask.Close();
  </code></pre>