The Data Export Plugin updates a single table in the designated database using OLE Db. A Data Group in the same DSD contains the data to be updated. Each row in the Data Group is updated to the table. Field names must match, case sensitive.
Updates will not be performed on a “schema” run of the DSD, but will be updated on a test run.
Transaction roll back is NOT available. Following clearance of duplicate records and the specified ClearSQL command, each record from the Data Group is added individually to the table.
Multiple instances of the Data Export plugin may be executed from the same DSD.
Parameters
| Connection | The id of a the fAdmin source for the database to be updated. Must have setting “update” set to “true” |
| Table | The table to be updated |
| Operation | The action to be taken – clear – clear all current records in the table – replace – replace existing records based on the unique key – append – add to the table records (e.g. audit) |
| DataGroup | The data group in the DSD that contains the data to update |
| UniqueKeyField | Optional. The field in the table that represent the unique key. Used when replacing data to remove existing records to be replaced. |
| ClearSQL | Optional. SQL WHERE clause that clears selected records from the table before updating. |
Process
The Data Group, Connection and target table are validated. Any error stops execution.
Pre-Clear Records
Depending on the “operation”, the following actions occur:
clear
All records in the target table are cleared.
replace
If the ClearSQL is specified it is run as “DELETE FROM [table] WHERE [ClearSQL]“. No checks are made on the clause.
If the UniqueKeyField is specified, each record in the target table with a matching value of the UniqueKeyField in the Data Group is deleted in turn.
Note that ClearSQL is faster and also capable of clearing records that no longer apply, whereas UniqueKeyField can only remove those that match new records.
append
No action is taken to clear records as all records will be appended.
Update
Each record in the Data Group is added to the target table.
Errors will occur on primary key violations, mis-matched fields and missing required fields.
Each record is updated individually and can fail individually. Errors are reported in fTest but not in LoadErrors.
