Bulk Copy (fast db insert)

• Bulk copy is usually used when we need to insert bigger amount of data at once.
• For bulk copy we need to have data in DataTable and define mappings to destination table.
• Define mappings is not necessary when source and destination table are equivalent.
• If we have identity column in destination table and we want destination to take care, we have to define mappings even though all other columns are equivalent. Defaulta mapping is Source[0] → Destination[0], Source[1] → Destination[1] etc..

      // define DataTable
      DataTable table = new DataTable("TableName");
      table.Columns.Add(new DataColumn("FkId",typeof(int)));
      table.Columns.Add(new DataColumn("Value1", typeof(double)));
      table.Columns.Add(new DataColumn("Value2", typeof(double)));
 
      // fill the table
      for (int i = 0; i < Length; i++)
      {
         table.Rows.Add(FkId[i], Value1[i], Value2[i]);
      }
 
      // define mappings and use bulk copy
      using (var bulkCopy = new SqlBulkCopy(_connectionString))
      {
          bulkCopy.BulkCopyTimeout = 600; // in seconds
          bulkCopy.DestinationTableName = "TableName";
          bulkCopy.ColumnMappings.Add("FkId", "FkId");
          bulkCopy.ColumnMappings.Add("Value1", "Value1");
          bulkCopy.ColumnMappings.Add("Value2", "Value2");
          bulkCopy.WriteToServer(table);
      }