Loading a batch into SQL Server from Rust

Article · Interrupted execution

Suppose an import has 50,000 stock adjustments. The first version inserts and commits each row separately. When row 31,004 fails, the operator needs to know which adjustments are already saved and what can safely be run again.

That is the problem to settle before making the import faster. Bulk copy can reduce the overhead of sending rows, but it does not decide the business transaction, validate every rule by default, or make a lost connection safe to retry.

The example here uses three rows instead of 50,000. It checks a rollback, a commit observed from another connection, and a rejected quantity. Those outcomes are easier to reason about at this size. Throughput measurements can come later, using a representative workload and a database owner who agrees on the resource budget.

Validate one bounded batch before opening the request

The Rust project pins Tiberius to 0.13.0. Its validate_batch helper accepts between one and 1,000 rows, requires positive IDs, rejects negative quantities and rejects repeated IDs within that slice. These are the rules for this example, not a general stock-adjustment model. A real system might allow negative changes while rejecting a negative final stock balance.

Validation happens before the bulk request starts. That avoids sending half of an input slice before discovering a duplicate further down the same slice. It does not check conflicts with existing database rows or make concurrent writers disappear. Database constraints and a deliberate transaction remain necessary.

The fixture creates a uniquely named disposable table with a primary key and CHECK(quantity>=0). The table name is generated by trusted code; it is not supplied by an upload. Table and column identifiers cannot be bound like values. This distinction matters because the bulk API interpolates identifiers into SQL.

This is the marked helper in sqlserver_bulk.rs. Its imports include tiberius::{IntoRow, SqlBulkCopyOption}; the full project contains the validation and connection helpers.

async fn send_batch(client: &mut SqlClient, table: &str, rows: &[(i32, i32)]) -> AnyResult<()> {
    validate_batch(rows)?;
    let mut request = client
        .bulk_insert_with_options(
            table,
            &["id", "quantity"],
            SqlBulkCopyOption::CheckConstraints.into(),
            &[],
        )
        .await?;
    for &(id, quantity) in rows {
        request.send((id, quantity).into_row()).await?;
    }
    let accepted = request.finalize().await?.total();
    assert_eq!(accepted, rows.len() as u64);
    Ok(())
}

The named columns make the mapping explicit: each tuple sends id then quantity. Keep that order when changing a schema. Both values are i32, so Rust would also accept a tuple that accidentally swapped them. Compare the saved values as well as the row count when adapting this helper.

Ask bulk copy to check the database rule

CheckConstraints is deliberate. Tiberius's default bulk operation does not enable CHECK constraint checking. The helper requests it explicitly rather than assuming that the table definition is enough. See the driver's bulk-copy options and bulk request API.

That option does not enable insert triggers; FireTriggers is separate. This example has no triggers. Before using the helper with an existing table, find out whether a trigger records audit data, updates another table, or rejects an invalid business operation. Bulk copy must preserve those required effects too. A passing quantity check says nothing about a trigger that never ran.

The application validator and server constraint need separate negative cases. The fixture first confirms that validate_batch(&[(4, -1)]) fails. Then it opens a separate transaction and deliberately bypasses that validator, sending the bad row through bulk copy with CheckConstraints still enabled. It consumes the request through send and finalize, and requires SQL Server error 547. Otherwise it has not checked the server rule independently.

After that failed request, the fixture discards its connection. Another connection verifies that the three previously committed rows are still the only rows. A production pool needs an equally explicit policy for interrupted or failed requests; it must not return a connection with an unfinished response to an unrelated caller.

Finishing the request does not finish the transaction

send may buffer data. The final finalize().await flushes remaining data and receives the bulk operation's result. Dropping the request after the loop is not an acceptable substitute. The BulkLoadRequest documentation explains why finalization is required.

The caller still owns the transaction. The complete fixture performs two controlled cases with the same three rows. In the first, it starts a transaction, sends and finalizes the batch, checks that the writer can see three rows, and rolls back. The next count must be zero. In the second, it sends the rows and commits. A fresh observer must then see three rows.

That observer matters. Seeing your own uncommitted inserts says less than confirming that another connection can see the committed result. Likewise, the driver's affected-row total is checked against the input length, but it is not treated as independent proof that the business transaction committed.

Run this example only in a disposable database, with the declared engine build, verified TLS and permission to create and drop its scratch table. The program requires ALLOW_DB_FIXTURES=yes and has a 30-second deadline. A forced interruption may leave the table behind, so record its generated name and have its owner remove that specific leftover. The flag does not make an existing business database disposable.

Decide what a retry means before adding it

If the connection breaks after sending COMMIT but before receiving its acknowledgement, an operator may not know whether the transaction committed. Replaying the same stock adjustment blindly could apply it twice. A timeout is not evidence of a rollback.

One possible application design assigns each import batch a durable unique ID and records completion in the same transaction as the imported rows. A later attempt checks that ID and compares the intended batch before deciding whether work remains. This fixture does not implement that protocol; its generated table and cleanup are for demonstration only. Do not mistake rerunning a disposable example for safe retries in an existing inventory system.

For a larger import, also choose whether the entire file must commit together or whether independently identified batches may commit separately. Smaller batches can reduce memory use and the time other requests wait behind a transaction, but partial progress becomes part of the user-facing behavior. Tell the operator which batch failed and which batches are already complete. Once those rules are settled, measure bulk sizes against the actual schema, indexes, constraints and concurrent workload. Keep the committed batch IDs in the operator's report alongside the failed one, so they can resume the import without guessing which rows were saved.