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");
programming/csharp/bulkcopy.txt · Last modified: 2018-06-21 19:48 (external edit)
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0