Update 1 column using bulkinsert C#

c# sql sqlbulkcopy

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());
     }
}
1
0
11/19/2013 11:58:18 AM

Popular Answer

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.

1
11/19/2013 12:18:50 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow