DuckDB

For DuckDB, the underlying implementation is leveraging the native DuckDBAppender class of DuckDB.NET (via the DuckDbBulkAppender wrapper).

For DuckDB, the underlying implementation is leveraging the native DuckDBAppender class of DuckDB.NET (via the DuckDbBulkAppender wrapper).

For BulkInsert, the entities/rows are written straight to the target table — unless DuckDbBulkImportIdentityBehavior.ReturnIdentity is requested, in which case a staging table is used instead so the generated identity values can be read back via a RETURNING clause on the very same statement that loads the real table.

For BulkDelete, BulkDeleteByKey, BulkMerge and BulkUpdate, a pseudo (staging) table is always created first. The library appends to it via DuckDbBulkAppender internally, then cascades the changes to the original table using the correct SQL statement.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

The other bulk operations can be optimized further by targeting the underlying table indexes (via qualifiers). Pass a list of Field objects when calling the operations.

Pseudo Table Type

The DuckDbBulkImportPseudoTableType enum lets you choose between a session-private TEMP table (Memory), an ordinary non-temporary table (Physical), or let the library decide (Auto, the default).

Auto’s row-count-based resolution is not implemented yet — it currently maps straight to Memory on every call, regardless of row count. Physical is only used when explicitly requested. Every bulk call also creates its own pseudo table (CREATE OR REPLACE [TEMP] TABLE ...) and drops it once the call completes, rather than creating one per (table, pseudo table type) and reusing it across calls.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>
• ExpandoObject
• IDictionary<string, object>

Operation SQL Statements

Once all the data is in the staging (pseudo) table, the correct SQL statement is used to cascade the changes towards the original table.

BulkInsert writes directly into the target table and skips the staging table entirely — unless identityBehavior is set to ReturnIdentity, in which case a staging table is used first (see above).

For BulkInsert

> INSERT INTO "OriginalTable" (Field1, Field2, ...)
> SELECT Field1, Field2, ... FROM "PseudoTempTable" ORDER BY rowid
> RETURNING "Identity";

This single statement is only used when identityBehavior is ReturnIdentity. Otherwise, DuckDbBulkAppender writes straight into "OriginalTable" and no SQL statement (or staging table) is involved at all.

For BulkDelete / BulkDeleteByKey

> DELETE FROM "OriginalTable" AS T
> WHERE EXISTS (
>     SELECT 1 FROM "PseudoTempTable" AS S
>     WHERE T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2
> );

For BulkMerge

> MERGE INTO "OriginalTable" T USING "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> WHEN MATCHED THEN UPDATE SET Field3 = S.Field3, Field4 = S.Field4
> WHEN NOT MATCHED THEN INSERT (Field1, Field2, ...) VALUES (S.Field1, S.Field2, ...);

The identity column, if any, is always left out of the INSERT column list — a brand-new row’s identity property is typically an unset default (e.g. 0), not a real value meant to be inserted as-is.

The single MERGE above is only used when identityBehavior is not ReturnIdentity. When it is, the library instead runs three separate statements — see Identity Setting Alignment below.

For BulkUpdate

> UPDATE "OriginalTable" AS T SET Field3 = S.Field3, Field4 = S.Field4
> FROM "PseudoTempTable" AS S
> WHERE T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2;

Unlike BulkMerge, staged rows with no matching target row are left as-is, not inserted.

Special Arguments

The arguments below are available on most operations.

Identity Setting Alignment

When identityBehavior is set to ReturnIdentity, DuckDB’s row order within the pseudo table’s rowid pseudocolumn is used to track each entity’s position in the source IEnumerable — no extra tracking column needs to be added to the staging table.

For BulkInsert, the library runs a single INSERT INTO ... SELECT ... FROM "PseudoTempTable" ORDER BY rowid RETURNING identity statement, then assigns the returned identities back to the entities in that (row) order.

For BulkMerge, a single MERGE ... RETURNING cannot both branch on match and read back generated identities in DuckDB, so the library instead runs three separate statements: (1) a LEFT JOIN snapshot between the staging and target table (ordered by the pseudo table’s rowid) to classify each staged row as matched (returning the target’s existing identity) or unmatched, (2) a plain UPDATE ... FROM for the matched rows only (skipped entirely if nothing matched), and (3) an insert-only INSERT ... WHERE NOT EXISTS ... RETURNING statement for the unmatched rows, reading back their new identities. This is not atomic — three round-trips instead of one — and the matched/unmatched classification from step 1 can go stale if another connection modifies the target table before steps 2 and 3 run.

BatchSize

All the provided operations have a batchSize argument that lets you override the number of rows wired-up to the server per batch. By default it is null, meaning all items are sent together in one-go.

Use this argument if you wish to optimize the operation based on certain situations.

• Network Latency
• Infrastructure
• No. of Columns
• Type of Data

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new DuckDBConnection(ConnectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

using (var connection = new DuckDBConnection(ConnectionString))
{
    var deletedRows = connection.BulkDelete<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkDeleteByKey

using (var connection = new DuckDBConnection(ConnectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

using (var connection = new DuckDBConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To return the newly generated identity values:

using (var connection = new DuckDBConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        identityBehavior: DuckDbBulkImportIdentityBehavior.ReturnIdentity);
}

BulkMerge

using (var connection = new DuckDBConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge(people);
}

Or with qualifiers.

using (var connection = new DuckDBConnection(ConnectionString))
{
    var mergedRows = connection.BulkMerge(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkUpdate

using (var connection = new DuckDBConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate(people);
}

Or with qualifiers.

using (var connection = new DuckDBConnection(ConnectionString))
{
    var updatedRows = connection.BulkUpdate(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}