Before you can insert data, you must specify the source
and other parameters, as described in this topic.
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 , 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:
- Map source and destination columns that are compatible,
but have unlike names.
- Specify destination column values other than the
source column values.
- Ignore specific columns.
- 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 .
- 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.
- 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 . 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.