General Tab

Before you can insert data, you must specify the source and other parameters, as described in this topic.

Source and other parameters specified on General tab of Insert Request Editor

Source File

Enter the name of the Archive or Extract File that contains the data to insert. By default, Extract Files have an .xf extension and Archive Files have an .af extension.

Control File

Enter the name of a Control File. This file is used during the process to track the success or failure of each row in the Archive or Extract File. Control Files have a .cf extension by default. If you specify the name for an existing Control File, a dialog will prompt you to confirm that you want to overwrite the file when you run the Insert Request. Use Personal Options to disable this feature.

Note: You can browse the contents of an Extract, Archive or Control File by clicking Utilities > Browse, or by right‑clicking and selecting Browse from the shortcut menu. For details on the Browse Utility, see the Common Elements Manual.

If you do not specify a path for file names, the request uses the drive and directory defined as the default Data Directory. See the Common Elements Manual for further information.

Table Map Options

Specify a Table Map to match tables in the Archive or Extract File with destination tables or to exclude tables from the request. You cannot save or process an Insert Request without a valid Table Map.

Within a Table Map, specify a Column Map for any pair of tables to:

Local
Select this option to create a Table Map to be used only with the active Insert Request. Local Table Maps are saved as part of the Insert Request.
Named
Select this option to create a new Table Map or select an existing Table Map to be used with the Insert Request. You must specify a name for the Table Map you want to create or the name of the existing Table Map you want to use.
Note: If changes have been made to the database since the last time the Table Map was used, the specifications may no longer be valid, and a warning is displayed.
Table Map Name
Name of the new or existing Table Map to use with the Insert Request. A Table Map name has two parts: identifier.name.
identifier
Identifier to identify the Table Map (1 to 8 characters).
name
Name of the Table Map (1 to 12 characters).
Always View Table Map
Select this check box to open the Table Map Editor any time you save or run an Insert Request. This option provides an opportunity to review the Table Map specifications before you insert the data. If you clear this check box, the Table Map Editor opens only when needed (for instance, when the specified Table Map does not include all the tables in the Archive or Extract File).

Select Edit Table Map from the Insert Request Editor to open the Table Map Editor. For details on how to create, edit, or merge Table Maps, see the Common Elements Manual.

Delete Options

Select options for removing all rows from specified tables before processing the Insert Request. Delete is useful for restoring data during testing. If a row cannot be deleted for any reason, all deleted rows up to the last commit are restored, and insert processing stops.

All Tables
Deletes all rows from all tables before processing. If you choose this option, you must also select a Delete Commit option. All Tables is valid only if the Process Option selected is Insert.
Mixed
Deletes all rows before processing from tables you select on the Insert Request Table Specification dialog. To display the Insert Request Table Specification dialog, select Tools > Edit Table Specification.
  • If you choose this option, you must also select a Delete Commit option.
  • All rows are deleted from tables you select in the Insert Request Table Specification dialog. If you do not select a table, the rows in that table are not deleted.
  • If you do not select any tables, the Delete Option changes from Mixed to No Tables.
  • If you select all of the tables, the Delete Option changes from Mixed to All Tables.
  • If you specify Mixed, and then save, run, or schedule the Insert Request, the Insert Request Table Specification dialog is displayed automatically.
Note: You cannot specify Mixed if the Process Option selected is Insert/Update or Update Only.
No Tables
Retains all rows in all tables before processing.

The Insert Request Table Specification dialog enables you to delete all rows before processing from selected tables.

Insert Request Table Specification dialog
Delete Commit
Select an option for committing changes to the database. These options are available only when you select All Tables or Mixed.
After Each Table
Commits changes to the destination database after deleting all rows from each selected table.
On Completion
Commits changes to the destination database after deleting all rows from all tables or all selected tables.

Process Options

Select the type of processing to be performed. Specify parameters to lock tables, set a commit frequency and set a discard row limit.

Insert
Inserts new rows into the tables.
  • If the primary key of a row in the source data does not match the primary key of a row in the destination table, the row is inserted.
  • If the primary key of a row in the source data matches the primary key of a row in the destination table, the row is bypassed and marked as discarded in the Control File.
Mixed
Updates, Inserts, or Updates/Inserts according to your selection for each table on the Insert Request Table Specification dialog. To display the Insert Request Table Specification dialog, click Tools > Edit Table Specification. Select a process option for each table.
  • If you select Mixed, and then save, run, or schedule the Insert Request, the Insert Request Table Specification dialog is displayed automatically.
  • If you select Mixed and do not specify selections on the Insert Request Table Specifications dialog, or you set all tables to the same selection, the Process Option changes to indicate the process used for all tables.
Note: You cannot select Mixed if the Delete Option specified is All Tables.
Update Only
Updates rows in the tables. Tables must have a primary key.
  • If the primary key of a row in the source data matches the primary key of a row in the destination table, the row is updated.
  • If the primary key of a row in the source data does not match the primary key of a row in the destination table, the row is reported as failed.
Note: You cannot select Update Only if the Delete Option specified is All Tables or Mixed.
Update/Insert
Updates and inserts rows in tables. Tables must have a primary key.
  • If the primary key of a row in the source data does not match the primary key of a row in the destination table, the row is inserted.
  • If the primary key of a row in the source data matches the primary key of a row in the destination table, the row is updated.
Note: You cannot select Update/Insert if the Delete Option specified is All Tables or Mixed.