Bulk Update in .NET with SQL Server

Bulk updating data is one of those tasks that looks simple but can quietly turn into a performance, maintainability, and safety nightmare if done the wrong way.

In this blog, we’ll walk through three clean, production‑ready approaches for bulk updates in .NET + SQL Server, written with clarity and long‑term safety in mind.

We’ll keep things minimal, understandable, and realistic — no magic, no hidden tricks.

---

Why Bulk Update Needs Special Care

Common mistakes developers make:

• Looping and updating row‑by‑row
• Hard‑coding JSON paths that break when DTOs change
• Overusing attributes on DTOs
• Ignoring SQL Server’s native strengths

Our goal is:

Fast, safe, schema‑aware bulk updates that survive future DTO refactors

---

The Sample Scenario

We’ll use a simple DTO throughout:

public class PurchaseDetailDto
{
    public int Id { get; set; }
    public int InventoryId { get; set; }
    public decimal Quantity { get; set; }
    public decimal Rate { get; set; }
}

And a SQL table:

Inventory.PurchaseDetail
(
    Id INT PRIMARY KEY,
    InventoryId INT,
    Quantity DECIMAL(18,2),
    Rate DECIMAL(18,2)
)

---

Approach 1: Temp Table + JOIN (The Classic Power Move)

When to use

• Very large datasets
• Maximum performance
• Full SQL control

Idea

• Bulk insert data into a temporary table
• Update target table using a JOIN

---

SQL Side

CREATE TABLE #TempPurchaseDetail
(
    Id INT,
    InventoryId INT,
    Quantity DECIMAL(18,2),
    Rate DECIMAL(18,2)
);

UPDATE t
SET
    t.InventoryId = s.InventoryId,
    t.Quantity = s.Quantity,
    t.Rate = s.Rate
FROM Inventory.PurchaseDetail t
JOIN #TempPurchaseDetail s ON s.Id = t.Id;

---

.NET Side (SqlBulkCopy)

using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
bulkCopy.DestinationTableName = "#TempPurchaseDetail";

bulkCopy.WriteToServer(dataTable);

---

Pros

✔ Fastest approach
✔ Battle‑tested
✔ Works with millions of rows

Cons

✘ Extra temp table
✘ Slightly more SQL setup

---

Approach 2: JSON + JOIN (Flexible & DTO‑Safe)

When to use

• APIs
• DTO‑driven systems
• Medium‑to‑large datasets

Key Idea

Instead of relying on JSON paths tied to property names, we:

• Build JSON manually using JsonSerializer
• Map DTO properties explicitly
• Avoid attributes completely

This protects us when DTO property names change.

---

Step 1: Safe JSON Serialization

var json = JsonSerializer.Serialize(items, new JsonSerializerOptions
{
    PropertyNamingPolicy = null
});

---

Step 2: Explicit Mapping in SQL

UPDATE t
SET
    t.InventoryId = j.InventoryId,
    t.Quantity = j.Quantity,
    t.Rate = j.Rate
FROM Inventory.PurchaseDetail t
JOIN OPENJSON(@json)
WITH
(
    Id INT '$.Id',
    InventoryId INT '$.InventoryId',
    Quantity DECIMAL(18,2) '$.Quantity',
    Rate DECIMAL(18,2) '$.Rate'
) j ON j.Id = t.Id;

---

Why explicit JSON paths are still OK

Even though you write paths like:

'$.InventoryId'

They are:

• Localized to SQL
• Easy to update
• Independent of DTO internals

No attributes. No reflection magic.

---

Pros

✔ Clean API integration
✔ No temp tables
✔ Easy to debug

Cons

✘ Slightly slower than temp tables
✘ Manual mapping required

---

Approach 3: Table‑Valued Parameters (TVP) — The Clean Contract

When to use

• Strong schema control
• Enterprise systems
• Reusable SQL contracts

---

Step 1: Create TVP Type

CREATE TYPE dbo.PurchaseDetailType AS TABLE
(
    Id INT,
    InventoryId INT,
    Quantity DECIMAL(18,2),
    Rate DECIMAL(18,2)
);

---

Step 2: SQL Update Using TVP

UPDATE t
SET
    t.InventoryId = s.InventoryId,
    t.Quantity = s.Quantity,
    t.Rate = s.Rate
FROM Inventory.PurchaseDetail t
JOIN @PurchaseDetails s ON s.Id = t.Id;

---

Step 3: .NET Usage

var table = new DataTable();
// fill columns + rows

var param = new SqlParameter("@PurchaseDetails", table)
{
    SqlDbType = SqlDbType.Structured,
    TypeName = "dbo.PurchaseDetailType"
};

---

Pros

✔ Strong typing
✔ No JSON
✔ Reusable & safe

Cons

✘ Schema changes require DB updates
✘ Slightly more setup

---

Final Comparison

Approach	Speed	Safety	Flexibility
Temp Table + JOIN	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐
JSON + JOIN	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐
TVP	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐

---

Final Thoughts

There is no single best approach — only the best approach for your use case.

• Need raw speed? → Temp tables
  
• API‑driven DTO system? → JSON + JOIN
• Strict enterprise contract? → TVP

Choose clarity first. Performance will follow.

Happy coding 🚀