BulkMerge_Lib

Create, update and delete thousands of entity records in one call, writing only what changed.

Requires: OutSystems 11 (.NET) with SQL Server, Azure SQL, Oracle 12.1.0.2+, PostgreSQL or MySQL.

Overview

BulkMerge_Lib has one server action, BulkMerge. Pass it a list of records of any entity and it:

1. Matches each record with its stored row by the entity's identifier.
2. Creates the records that have no row, and returns them with their new identifiers.
3. Updates the records that differ from their row, and returns them with the rows as they were before.
4. Skips the records identical to their row.
5. Optionally deletes the rows whose identifier isn't in the list.

It all runs in the request's transaction.

Getting started

1. Add a dependency on BulkMerge_Lib and select the BulkMerge action.
2. Build a list of a single entity: a list of the entity's type, or an aggregate's list holding only that entity, with every attribute fetched.
3. Set RecordList to ToObject(YourList).
4. Read the counts. To go through the returned records, use the Record Manipulation Forge component.

Inputs

Outputs

The record lists have the same type as RecordList, and are empty when ReturnAffectedRecords is False.

How records are compared

Every attribute is compared except the identifier and those in IgnoredAttributesCSV. If any differs, the record is written in full.

• Text is compared exactly: a change only in case, accents or trailing spaces counts, whatever the column's collation.
• Binary data is compared byte by byte.
• NULLs: in a non-mandatory attribute, a NULL equals the attribute's default value (the entity's own, e.g. -1), as the platform reads it. In a mandatory attribute, a NULL differs from any value. On Oracle, an empty text is a NULL.
• ConvertDefaultValuesToNull doesn't rewrite a stored default as NULL: the two count as the same.

Ignored attributes are still written when another attribute differs. That suits ModifiedAt, which then changes only on real changes. For CreatedAt or CreatedBy, make sure the records carry their stored values, or an update overwrites them.

Output order and identifiers

• CreatedRecords, UpdatedRecords and BeforeUpdateRecords follow the order of RecordList. If the list holds 1333, 1334 and 1335 and only 1333 and 1335 changed, UpdatedRecords holds 1333 then 1335.
• The record at index n of BeforeUpdateRecords is the stored row of the record at index n of UpdatedRecords.
• With an auto-number identifier, a record whose identifier is empty, or isn't in the table, gets a new identifier, and CreatedRecords holds it. RecordList itself isn't changed.
• An empty RecordList does nothing, unless DeleteWhenNotMatched is True.

Deleting: every row missing from RecordList is deleted, so pass the complete list. A filtered or paged list deletes the rest of the table.

Databases

• SQL Server, Azure SQL: bulk copies into a temporary table.
• PostgreSQL: uses a temporary table; needs the TEMP privilege, granted by default.
• MySQL: uses a temporary table; needs the CREATE TEMPORARY TABLES privilege.
• Oracle 12.1.0.2+: no temporary table, since creating one commits the transaction; the stored rows are read and compared in the application.

For a table in another database, set DBConnectionName.

Performance

Indicative times for 300,000 records, measured on a laptop with each database in a container:

Set ReturnAffectedRecords to False when you only need the counts, and raise TimeoutSeconds for large lists: the default of 60 applies to each statement.

Errors

BulkMerge raises an exception starting with BulkMerge: before writing anything. If unhandled, the request's transaction rolls back.

Limitations

• One identifier attribute per entity; OutSystems 11 on .NET only.
• On SQL Server, a Text attribute in a uniqueidentifier column can't be bulk copied.
• The table isn't locked: rows other transactions change meanwhile are compared as read.
• With ReturnAffectedRecords False on SQL Server, DeletedCount can include rows changed by triggers.