title: How to do a batch update?
body: <p>I am wondering is there a way to do batch updating? I am using ms sql server 2005.</p>
  
  <p>I saw away with the sqlDataAdaptor but it seems like you have to first the select statement with it, then fill some dataset and make changes to dataset.</p>
  
  <p>Now I am using linq to sql to do the select so I want to try to keep it that way. However it is too slow to do massive updates. So is there away that I can keep my linq to sql(for the select part) but using something different to do the mass update?</p>
  
  <p>Thanks</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I am interested in this staging table way but I am not sure how to do it and still not clear how it will be faster since I don't understand how the update part works.</p>
  
  <p>So can anyone show me how this would work and how to deal with concurrent connections?</p>
  
  <p><strong>Edit2</strong></p>
  
  <p>This was my latest attempt at trying to do a mass update using xml however it uses to much resources and my shared hosting does not allow it to go through. So I need a different way so thats why I am not looking into a staging table.</p>
  
  <pre><code>using (TestDataContext db = new TestDataContext())
              {
                  UserTable[] testRecords = new UserTable[2];
                  for (int count = 0; count &lt; 2; count++)
                  {
                      UserTable testRecord = new UserTable();
  
                      if (count == 1)
                      {
                          testRecord.CreateDate = new DateTime(2050, 5, 10);
                          testRecord.AnotherField = true;
                      }
                      else
                      {
                          testRecord.CreateDate = new DateTime(2015, 5, 10);
                          testRecord.AnotherField = false;
                      }
  
  
                      testRecords[count] = testRecord;
                  }
  
                  StringBuilder sBuilder = new StringBuilder();
                  System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
                  XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
                  serializer.Serialize(sWriter, testRecords);             
  
                  using (SqlConnection con = new SqlConnection(connectionString))
                  {
                      string sprocName = "spTEST_UpdateTEST_TEST";
  
                      using (SqlCommand cmd = new SqlCommand(sprocName, con))
                      {
                          cmd.CommandType = CommandType.StoredProcedure;
  
                          cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
                          SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
                          param1.Value = sBuilder.Remove(0, 41).ToString();
                          cmd.Parameters.Add(param1);
                          con.Open();
                          int result = cmd.ExecuteNonQuery();
                          con.Close();
                      }
                  }
              }
  </code></pre>
  
  <p>@ Fredrik Johansson I am not sure what your saying will work. Like it seems to me you want me to make a update statement for each record. I can't do that since I will have need update 1 to 50,000+ records and I will not know till that point.</p>
  
  <p><strong>Edit 3</strong></p>
  
  <p>So this is my SP now. I think it should be able to do concurrent connections but I wanted to make sure.</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[sp_MassUpdate]
  @BatchNumber uniqueidentifier 
  AS
  BEGIN
      update Product
      set ProductQty = 50
      from Product prod
      join StagingTbl stage on prod.ProductId = stage.ProductId
      where stage.BatchNumber = @BatchNumber
  
      DELETE FROM StagingTbl
      WHERE BatchNumber = @BatchNumber
  
  END
  </code></pre>