SQL Bulk Update - C# (BulkCopy)

SQL Bulk Update - C# (BulkCopy)

Using C# (SQL BulkCopy)

Pritom Purkayasta's photo
Pritom Purkayasta
ยทFeb 25, 2021ยท

3 min read

Subscribe to my newsletter and never miss my upcoming articles

Play this article

Table of contents

  • Introduction:
  • Code
  • Steps:
  • Repo


Bulk update is a topic that many .net developers are struggling to figure out. There is a way to get it working as Microsoft provides a SqlBulkCopy.

For bulk insertion you can check my previous article Bulk Insertion

To work with SqlBulkCopy class, the only requirement is to convert your list to a DataTable instance.

If you are coming from my previous Bulk Insertion then this process will be familiar to you.


private DataTable ToDataTable(List<T> items)
  DataTable dataTable = new DataTable(typeof(T).Name);
  PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  foreach (PropertyInfo prop in Props)
  foreach (T item in items)
    var values = new object[Props.Length];
    for (int i = 0; i < Props.Length; i++)
      values[i] = Props[i].GetValue(item, null);
  return dataTable;


So there is no magic method to do a bulk update on the database using c#. But we can have some workaround on it. There are some steps to achieve that.

  • Create a temp table.
  • Insert all the data into the temp table.
  • Update from that temp table to destination table.
  • Drop temp table.

And That should do it. ๐Ÿ˜

So let's go into the code.

But at first, create SqlConnection first.

using (SqlConnection conn = new SqlConnection(_connectionString))

I have created an SQL command txt to create the temp table. You can do however you feel comfortable.

string GetTempTableCreateCmd(DataTable dataTable, string tempTable)
  StringBuilder columnTxt = new StringBuilder();
  columnTxt.Append($"CREATE TABLE {tempTable}(");
  int columnCount = dataTable.Columns.Count;
  for (int i = 0; i < columnCount; i++)
    string dataType = dataTable.Columns[i].DataType == Type.GetType("System.String") ? "VARCHAR(100) " : dataTable.Columns[i].DataType.ToString();
    string colum = $"{dataTable.Columns[i]} {dataType}";
    if (i != columnCount - 1)
      columnTxt.Append(", ");
  return columnTxt.ToString();

Now execute this.

string tempTableTxtCmd = GetTempTableCreateCmd(datatable, tempTableName);
ExecuteCmd(tempTableTxtCmd, conn);

private void ExecuteCmd(string cmdTxt, SqlConnection connection)
  using (SqlCommand cmd = new SqlCommand(cmdTxt, connection))

Now it's time to insert to the temp table. For this checkout Bulk Insert C#

So temp table creation is complete. Now coming to our third step is to update from the temp table to our destination/main table. Let's write some SQL commands to do that.

string GetOriginalTblToTempTableUpdateCmd(DataTable dataTable, string originalTable, string tempTable)
  StringBuilder updateTblCmd = new StringBuilder();
  updateTblCmd.Append("UPDATE ORGI SET ");

  for (int i = 1; i < dataTable.Columns.Count; i++)
    updateTblCmd.Append($"ORGI.{dataTable.Columns[i]} = TEMP.{dataTable.Columns[i]}");

    if (i != dataTable.Columns.Count - 1)
      updateTblCmd.Append(", ");

  updateTblCmd.Append($" FROM {tempTable} TEMP INNER JOIN {originalTable} ORGI ON ORGI.{dataTable.Columns[0]} = TEMP.{dataTable.Columns[0]}");

  return updateTblCmd.ToString();

Now it's time to execute it also. ExecuteCmd(updateFromTempTableCmd, conn);

Finally, it's time for the last step that is to drop the temp table. This should do the job.

var dropTempTableCmd = $"DROP TABLE {tempTableName}";
ExecuteCmd(dropTempTableCmd, conn);

And that's it โœ…


For full code visit Github Repo

Share this