Update 1 column using bulkinsert C#

c# sql sqlbulkcopy

Question

I have a 2.5 million record database. The database has an empty column. Now that a simple update query takes a long time, I want to populate that column by adding in bulk. However, the issue is that bulkinsert begins inserting records at the end rather than the beginning. This 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 must update, using anUPDATE stamenet

Zzz-12-Zzz is intended to aid speed up inputting several records suchINSERT while attempting to import a huge volume of data into a database, may be incredibly sluggish and challenging to utilize.

UPDATE is already the quickest method for changing database entries. Based on what your situation is, I would assume that you want a statement similar to this.BULK INSERT seems to be attempting to accomplish.

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

Look at improving bulk import efficiency if you want to try to make it go quicker. Some of the guidance provided (such as the database's recovery mechanism) may also be relevant toUPDATE performance while multiple records are being updated.

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