FireDAC: Bulk insert using Array DML with clean per-row error handling

A BDE-replacement with native binding bulk insert using Array DML is often the fastest way to get many records into a database: instead of a thousand individual inserts you bind a parameter array and send it to the server in one go. In practice the sticking point appears quickly: a record violates a unique index, a NOT NULL field is empty, a foreign key does not match — and suddenly it is unclear which row killed the batch, whether part of it has already been written and how to continue cleanly without creating data inconsistencies.

That is exactly what this is about: how to use Array DML so that you get reliable error information per row, keep the transaction under control and can trace in operation what happened. The focus is not on academic API reading, but on the edge case that regularly occurs in real imports: a large batch, a few broken rows, but you still want speed.

FireDAC bulk insert with Array DML: why Array DML is worth it for bulk inserts

Array DML (Data Manipulation Language) in FireDAC means: you bind parameters not as single values but as arrays. FireDAC then sends (depending on driver/DB) fewer roundtrips, can work more efficiently on the server side and drastically reduces client overhead. This matters particularly in three situations:

• ETL and import pipelines: CSV/XML/JSON in, normalization/mapping, then into a staging or target table.
• Interface buffers: REST- or MQ payloads are accumulated and periodically persisted.
• Audit / event tables: many small inserts where latency dominates.

The benefit does not come for free. With Array DML you shift complexity from “many individual statements” to “one statement with many rows”. That is good for performance, but more demanding for error diagnosis, transaction logic and restart/retry.

The typical edge case: one batch, one broken row

The common production scenario: you import 50,000 rows. You choose an ArraySize of 1,000 because you do not want a roundtrip per row. Batch 17 fails. The DB reports only “duplicate key” or “violates foreign key constraint”. In the UI or service log you then often see only: „ExecSQL failed“.

Without solid error handling two bad outcomes usually happen:

• You discard the whole batch, even though 999 of 1,000 rows would have been fine.
• You fall back to single inserts and permanently lose the performance advantage.

The goal is a third way: retain batch performance, but log defects precisely (row index, key values, DB error text) and optionally commit “good rows” — depending on how critical consistency and idempotence (running multiple times without duplicate effect) are in your process.

FireDAC Array DML: The relevant levers (no myths)

For bulk insert with Array DML the same controls are decisive in practice:

1) ArraySize and batch size

ArraySize (for TFDQuery/TFDCommand) determines how many “rows” FireDAC are processed in a single call. Larger is not automatically better. Too large means: more client memory, more payload on the wire, larger locks/log load on the server and, in case of error, a bigger blast radius. For robust imports a batch size between 200 and 2,000 is often a good starting point, depending on column count, BLOBs and latency.

2) Transaction boundary

You need a clear decision: commit per batch or commit for the entire import. This is not a matter of taste but an operational decision:

• Commit per batch: limits locks and transaction log, simpler RESTart, but intermediate states are visible (depending on isolation level). Errors in batch 17 leave batches 1–16 in the system.
• Commit at the end: “all or nothing”, more consistent from a business perspective, but with large volumes you risk long locks, big rollbacks and in case of error everything is lost.

For many interface and import processes, “commit per batch” is the more realistic operational strategy — but only if you have idempotence and a duplicate-handling strategy properly defined (e.g. via natural keys, upserts or an import ID).

3) UpdateOptions and prepared statements

For repeated batches it pays to keep the statement prepared. “Prepare” means: FireDAC lets the DB parse/compile the statement and reuse it. Depending on the DB this can have a noticeable effect, especially at high frequency. What matters here is less a “trick 17” and more: consistent reuse of the same query object (or the same TFDCommand) and stable parameter types.

Clean error handling per row: what you really need

If you want to handle errors “per row”, you need three things:

1. Mapping: Which array index (0..N-1) failed?
2. Context: Which business key values does this row have (e.g. external ID, customer number, timestamp)?
3. Control: What do you do afterwards? Abort, skip only the bad rows, or split the batch?

FireDAC can, depending on the driver, return errors per array element. In practice that is not “simply always there”. You must expect that some databases/providers report only the first error or that an error in the batch prevents the REST from executing. Precisely for that reason a robust pattern is usually two-stage:

• Stage A: Attempt the batch as Array DML.
• Stage B: If the batch fails, split it (halve it) or fall back to individual rows in a controlled way — but only for that batch — and log cleanly.

That sounds like extra work, but in import pipelines it is the difference between “everything stops at 02:00” and “the import continues; 7 rows end up in the error list”.

A practical pattern: batch first, then isolate selectively

The following pattern has proven effective for process-oriented software solutions where data quality is mixed:

Step 1: Put data into a batch structure (including error context)

Store the data to be imported not only as raw values, but with minimal context: external ID, source row number, possibly a hash/checksum. This is not “nice to have”: in case of an error you do not want to parse the CSV again to find out what is broken.

Step 2: Execute Array DML

You set ArraySize to the batch length, bind parameters as arrays, and call ExecSQL. Important: keep parameter types stable (e.g. do not bind numeric fields sometimes as string and sometimes as integer), otherwise the DB produces implicit casts or FireDAC has to convert per element.

Step 3: Error case – narrow the batch instead of blindly retrying

If ExecSQL fails, you have two robust options:

• Binary Split (halve): split the batch into two halves, try each half again as Array DML. Repeat until you reach a small amount you can inspect individually. Advantage: you retain much of the performance when only a few rows are bad. Disadvantage: more logic, and it helps little for systematic errors (e.g. wrong data type).
• Fallback to single rows for this batch: set ArraySize=1 (or bind single values) and execute row by row, log errors and proceed. Advantage: simple, guaranteed per row. Disadvantage: you lose speed for this batch.

In practice I combine both: first split 1–2 times (to quickly get “good blocks” through), then switch to single rows for small remainders to log definitive error information.

Error objects and messages: What you should extract from FireDAC

FireDAC encapsulates DB errors in exceptions (typically EFDDBEngineException) with detailed information. For operations three levels are important:

• DB error code (DB-specific): e.g. SQLSTATE for PostgreSQL, Error Number for SQL Server.
• Constraint/object name: often included in the error text (unique index, FK constraint).
• Statement context: table, operation, possibly parameter values (be cautious with personal data).

If you want to log per row, you must also identify the row in case of an error. FireDAC may, in some cases, provide the array index. Do not rely solely on that. Always create an additional index yourself (position in the batch) and log at that position at least one business key.

Pitfalls that cost time in real imports

1) „It was only one row“ – but the transaction is already „dirty“

Depending on the DB and driver, an error can cause the entire statement execution to be considered failed and the transaction to be in a state where you must explicitly roll back or where further statements will fail. With certain drivers, „just continue after an error“ is not a safe assumption.

Consequence: If you work inside a transaction and a batch fails, the standard path is: rollback the current batch context (or the entire transaction) and then RESTart. This fits well with „commit per batch“.

2) Autocommit vs. explicit transaction

If you do not start an explicit transaction, the driver/provider often decides how it commits statements. For bulk imports that is seldom what you want. Explicit transactions give you control over:

• lock duration
• Rollback behavior
• Restart points

And: Explicit does not mean “one huge transaction”. It means “intentional”.

3) Triggers, constraints and side effects

Array DML speeds up the handover, but not automatically the server-side work. If you have triggers on the target table (e.g. audit logging, automatic status calculation), the bottleneck may not be the insert at all, but the trigger code. A batch can reduce roundtrips, yet CPU on the DB server remains the limiting factor.

For admins and technical leads: with performance problems, look at wait events/locks and the transaction log. The bulk insert is then only the trigger, not the root cause.

4) Data types and implicit conversions

One of the most common “Why is this slow?” reasons: parameters are bound as strings and the DB casts each row to integer/date/decimal. That is invisible but expensive. For stable performance:

• Set parameter data types appropriately (date as date, number as number).
• Watch out for locale pitfalls with decimals (comma vs. dot). FireDAC is usually correct here, but mixed sources are not.
• Clarify timezone/UTC strategy in advance (timestamps are a classic issue in imports).

5) Error messages are for humans, not for automation

It’s tempting to parse the error message (“duplicate key value violates unique constraint …”). Do that only as a last resort. Structured codes (SQLSTATE, error number) are better. Unfortunately not all drivers provide everything equally well. Plan for both: code and text, plus optionally “constraint name from text”, but without hard dependency.

Debugging notes: How to quickly find the broken row

Make the batch reproducible

If an import fails sporadically, you need reproducibility. Store a small diagnostic file or a log entry per batch that contains:

• batch number and time
• ArraySize and transaction mode
• the list of business keys (e.g. external IDs) in the batch

That is often enough to run a focused mini-import for just those IDs afterwards.

Make the final SQL visible (but without data leaks)

In debugging you want to know: Is the SQL correct? Are the parameters right? FireDAC provides monitoring/tracing via FDMoni components and driver logging. In production-like environments it is important to:

• enable tracing only temporarily and selectively (performance and privacy).
• log parameter values only in a secure environment or masked.
• for personal data: log only technical keys (IDs) and no plaintext content.

If you split-test: define abort criteria

When you do a binary split you don’t want to divide indefinitely. Set a lower bound, e.g. “switch to single-row mode under 20 rows”. And set a limit on how many errors you tolerate overall before aborting the import (e.g. for systematic mapping problems). Otherwise you end up with endless error lists and block downstream processing.

When the effort is really worth it (and when it is not)

Array DML with per-row error handling is particularly worthwhile when:

• many rows are processed (thousands to millions).
• few rows are faulty, but you still want to proceed.
• the import must run stably in production (e.g. night processing, service without UI).
• you need to return an error list to the business unit/source (with row reference).

It’s less worthwhile when:

• you only write a few dozen lines (single inserts are OK),
• the data quality is so poor that 30–50% of the rows fail (then a staging strategy is more sensible),
• you already use a DB-native bulk-load procedure (e.g. COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – then Array DML is not the tool.

Alternative architecture: staging table instead of „direct to target“

When you regularly struggle with mixed data quality, a pure „insert directly into the target table“ is often the wrong decision. A staging table (pre-stage) is a table where you first store data in a technically correct way (possibly with loose types), and only afterwards validate and transfer it into the target table.

Operational advantages:

• Erroneous records remain stored traceably (including raw data).
• You can run validation separately and repeatably.
• You decouple interface acceptance from domain processing.

Array DML is often the fast path into the staging table, while the transfer into the target table is performed as set-based SQL (or Stored Procedure). That shifts error handling more to the DB side, which depending on the organisation (DBA roles, deployment) can be appropriate or undesirable.

Operations and administration: what IT leads and admins should know

Monitoring: error rate and throughput are the core metrics

For stable operation of a bulk import, two metrics are more informative than „runtime“ alone:

• Throughput: rows per minute (or per batch) including peak/median.
• Error rate: failed rows per run, ideally grouped by error class (unique, FK, NOT NULL, type conflict).

If you regularly monitor these two values, you will detect early whether something at the source has changed (e.g. new format) or whether the target system (e.g. new constraints) has become stricter.

Locking and load windows

Bulk inserts can generate locking and I/O load. If users operate concurrently on the same tables, you need to consider isolation level, indexes and, where applicable, partitioning. Practically this means: either schedule imports in load windows, or design the data flow so it coexists with ongoing operation (e.g. via staging + asynchronous takeover).

Concrete checklist for a robust bulk insert with Array DML

• Batch size: set (initial value 500–1,000) and tune measurably.
• Explicit transaction: commit per batch as the default; „commit at the end“ only intentionally.
• Stable parameter types: set types explicitly, avoid forcing implicit casts.
• Carry error context per record (external ID, source row).
• Error strategy: batch first, then split/fallback, log per row.
• Logging: codes + text, but data-protection compliant; capture batch ID and run ID.
• Retry: ensure idempotence (key/upsert/import ID).

Conclusion: Array DML is fast — it becomes robust through process and error strategy

A FireDAC Bulk-Insert with Array DML is a powerful tool, as long as you don’t pretend there are no errors. In real data streams there are always outliers: duplicates, missing references, corrupted date values. The proper approach is therefore: Array DML for performance, combined with a controlled isolation strategy (Split or Fallback) and a per-row traceable error list. That gives you speed and operational reliability together — and that’s exactly what matters when imports must run reliably every night, not just in the lab.

If you want to stabilize an existing import or interface process in Delphi/FireDAC (performance, transactions, restartability, logging), we are happy to clarify this in a structured technical discussion:

For this topic, Delphi Bulk Insert and Bulk Insert Delphi FireDAC are also important. The article places these aspects in a clear context and shows what matters in daily operations.

Discuss a project or modernization initiative with Net-Base.