Update 1 column using bulkinsert C#

Question

I have dataabase of 2.5 million records. One column in database is empty. Now i want to fill that column by inserting in Bulk, because a simple update query takes a lot of time. But the problem is that bulkinsert starts inserting record at the end, not from the start. Here is my code:

using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
     for (int j = 0; j < rows.Length; j++)
     {
         DataRow dailyProductSalesRow = prodSalesData.NewRow();                   
         string[] temp = ((string)rows[j]["Area"]).Split(new string[] { " ", "A","a" }, StringSplitOptions.None);
         if (Int32.TryParse(temp[0], out number))
         {
             int num1 = Int32.Parse(temp[0]);
             dailyProductSalesRow["Area1"] = 1;
         }
         else
         {
             dailyProductSalesRow["Area1"] = 0;
             Console.WriteLine("Invalid "+temp[0]);
         }
         prodSalesData.Rows.Add(dailyProductSalesRow);
     }
     s.DestinationTableName = prodSalesData.TableName;

     foreach (var column in prodSalesData.Columns)
     {
         s.ColumnMappings.Add(column.ToString(), column.ToString());
     }
     try
     {
         s.WriteToServer(prodSalesData);
     }
     catch (Exception e)
     {
         Console.WriteLine(e.ToString());
     }
}

I have to read data from file then perform some transformations and then insert it to database that's why doing in c# — Zia ur Rehman, Nov 19 '13 at 11:44

Justin · Answer 1 · 2013-11-19T12:18:50.913

If you need to update, use an UPDATE stamenet

BULK INSERT is designed to help make inserting many records faster as INSERT can be very slow and difficult to use when trying to get a large amount of data into a database.

UPDATE is already the fastest way of updating records in a database. In your case I'd guess you want a statement a bit like this based on what your BULK INSERT is apparently trying to do.

UPDATE SalesData
SET Area1 = CONVERT(INT, SUBSTRING(Area, 0, CHARINDEX('A', Area)))

If you want to try and make it faster then try looking at Optimizing Bulk Import Performance. Some of the advice there (e.g. the recovery model used by the database) may also be applicable to UPDATE performance when updating many records.

Update 1 column using bulkinsert C#

1 Answers1