Database Table Actions Settings
Whenever you create a mapping connection to a database table, a Database Actions button appears next to the affected table. Clicking this button opens Database Table Actions dialog box, from where you can configure the database insert, update, and delete actions, as well as other options.
Database Table Actions dialog box
Below is a description of the settings available on the Database Table Actions dialog box.
SQL statement to execute before first record
In this group box, you can define SQL statements that are executed before any actions defined under Actions to execute for each record. Select the desired radio button:
•None — No action is carried through. This is the default setting.
•DELETE all records — All records from the selected table are deleted before any specific table action defined in the Actions to execute for each record group box is performed. Activate the also delete all records in all child tables check box if you also want to get rid of the data stored in child tables of the selected table. For an example, see Inserting Data into Multiple Linked Tables.
•Custom SQL — Write a custom SQL statement to affect the complete table. Note that support for multiple SQL statements in one query depends on the database, connection method, and the driver used.
Actions to execute for each record
This group of settings specify the database actions that are to take place against this table when the mapping runs. To manage table actions, click the Append Action, Insert Action, or Delete Action buttons. Multiple actions can be defined if necessary (for example, an "Update if..." action followed by an "Insert Rest" action.
The defined table actions are processed from left to right. In the example above, the "Update if..." action is processed first. If the update condition is not satisfied then the following action is processed (in this example, the "Insert Rest" action). Note the following:
•All the defined conditions of one action must be satisfied for the table action to be executed. When this is the case, all those fields are updated where a connection exists between the source and target items on the mapping. Any subsequent table actions (to the right of an action whose condition matched) are ignored for that record.
•If the defined condition is not satisfied, then the table action is skipped, and the next action (to the right) is processed.
•If none of the conditions are satisfied, no table action takes place.
Any table actions defined after "Insert All" or "Insert Rest" actions will never be executed, because no column conditions exist for insert actions. A dialog box appears if this is the case, stating that the subsequent table action columns will be deleted. |
The NULL Equal check box next to each record, where applicable, instructs MapForce that null values in the source record must be treated as equal with null values in the target record. Not selecting this check box may lead to incorrect results, see Handling Nulls in Database Table Actions.
When the mapping updates a table which has foreign key relationships to other tables, the following options can be used:
Delete data in child tables | This option is meaningful when you select the "Update if..." action for a parent table. It might be necessary if the number of records in the source file is different from the number of records in the target database, and you want to keep the database synchronized (no orphaned data in child tables). See also Options for Child Tables When Updating a Parent Table. |
Ignore input child data | Use this option when you want to update a target parent table, without affecting any of the child tables/records of that table. See also Options for Child Tables When Updating a Parent Table. |
For examples which illustrate various combinations of actions, see:
•Inserting Data into Multiple Linked Tables
•Options for Child Tables When Updating a Parent Table
•"Update if... Insert Rest" Action
Use Transactions
Enables database transactions for this particular table action. For more information, see Handling Database Exceptions.
Use Bulk Transfer
Enables bulk transfer (multiple INSERT statements as one query). Bulk transfer is supported if the mapping is executed by MapForce Server and the database action is "Insert All". For more information, see Bulk Inserts (MapForce Server).