To insert data, you must identify the source and provide
other parameters to control processing.
Source File
The
name of an Archive or Extract File used as the basis for the Insert
Request. This file provides the data model for a Table Map created
from within the Insert Request and, unless overridden, the data to
be inserted. If you do not provide a path with file names, the default
Data Directory defined in Personal Options is used.
Control File
The
name of a Control File, used to track the success or failure of processing
each row in the source file. The .cf extension
is added by default.
If you enter the name of
an existing file, a dialog prompts to confirm that you want to overwrite
the file when you run the Insert Request. To disable the confirmation
prompt, use Personal Options.
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, refer to the Common
Elements Manual.
Table Map Options
A Table Map used
to match data in the source file to destination tables or to exclude
data from selected tables from processing. An Insert Request must
reference a valid Table Map in order to be saved or processed. Click to display the Table Map referenced for Insert processing.
For details on how to create, edit, or merge Table Maps, refer to
the Common Elements Manual.
A Table Map
may reference a Column Map for any pair of tables in order to:
- Map compatible source and destination columns that
have dissimilar names.
- Transform source column values before inserting
them into destination columns.
- Bypass processing for specific columns.
- Local
- Embed a Table Map in the Insert Request. Local Table Maps are
saved as part of the Insert Request and can only be used with it.
- Named
- Create a new Table Map or select an existing Table Map to use
with the Insert Request. You must provide a name for the Table Map.
Note: If
the database structure has changed since the Table Map was last used,
a warning is displayed.
- Table Map Name
- The Table Map used for Insert processing.
- identifier
- Identifier (1 to 8 characters) for the Table Map.
- name
- Name of the Table Map (1 to 12 characters).
- Always View Table Map
- Display the Table Map each time you save or run the Insert Request.
Clear this check box to display the Table Map only when necessary
(for example, when the Table Map data structure does not match that
of data in the source file).
Delete Options
Options
for deleting data from destination tables before Insert processing.
Delete is useful for refreshing data during testing. If a row cannot
be deleted for any reason, any rows deleted since the last commit
are restored, and insert processing stops. Note that the Insert Delete
Options do not govern a relational delete; to perform a relational
delete, you must click in the main window.
- All Tables
- Delete all data from all tables before Inserting
source data. If you choose this option, you must also select a Delete
Commit option.
Note: Valid with the Insert Process
Option only.
- Mixed
- Before Insert processing, delete all data from tables
selected on the Insert Request Table Specification dialog.
(See Insert Request Table Specification Dialog for more information.) If you choose this option,
you must also select a Delete Commit option.
Note: Valid
with the Insert or Mixed Process Option only.
- No Tables
- Retain all rows in all tables.
- Delete Commit
- Options for committing database deletions when All
Tables or Mixed is selected.
- 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
Options
for the type of processing to be used. Additional parameters allow
you to lock tables, set a commit frequency and limit the number of
discarded rows.
- Insert
- Insert rows into the tables. If the primary key
value for a source table row does not match that of a row in the destination
table, the process inserts the row. If the primary key value for a
source row matches that of a row in the destination table, the source
row is not processed and is marked as discarded in the Control File.
- Update Only
- Update rows in the tables. If the primary key value
for a source table row matches that of a row in the destination table,
the process updates the row If the primary key value for a source
row does not match that of a row in the destination table, the source
row is not processed and is marked as failed in the Control File.
Note: Update
Only is invalid if the Delete Option is All
Tables or Mixed.
- Update/Insert
- Insert and update rows. If the primary key value
for a source table row does not match that of a row in the destination
table, the process inserts the row. If the primary key value for a
source row matches that of a row in the destination table, the process
updates the destination row.
Note: Update/Insert is
invalid if the Delete Option is All
Tables or Mixed.
- Mixed
- Process each table according to the corresponding
selection on the Insert Request Table Specification dialog.
(See Insert Request Table Specification Dialog for more information.)
Note: Mixed is
invalid if the Delete Option is All
Tables.
- Lock Tables
- Lock each table while source rows are processed.
Use Lock Tables to ensure that other database activity does not interfere
with the process, taking into consideration, that locking prevents
other users from accessing a table during processing. If a Product
Options setting prevents table locks, this option is disabled.
- Process File Attachments
- Process file attachments in the Source File. Each
file attachment is restored to a location provided by the Directory
Map. (For more information, refer to Directory Map Dialog.)
- Commit Frequency
This option is disabled if Lock Tables is
selected. Enter the number of rows to process before committing the
changes to the database. Clear this box to use the Maximum
Commit Frequency specified in Product Options.
(Refer to the Installation and Configuration Guide .) Frequent commits keep locking to a minimum,
but might slow the process.
If the process ends
abnormally, click from the menu to
resume processing from the last commit point. Refer to the Common
Elements Manual.
- Discard Row Limit
Enter the maximum number of rows that can be
discarded, up to 999999999. To allow an unlimited number of rows to
be discarded, specify zero (0) or leave blank.
The process stops after the limit is reached and all
rows in the array are processed, allowing you to troubleshoot or modify
the Insert Request. You can then click from the menu to
resume processing at the point at which the process stopped.
Disable Triggers
Options
for processing database triggers during the Insert Process. You can
disable triggers for Oracle, Informix®,
and SQL Server (Version 7 or later), and Sybase ASE (Version 12 or
later).
- Always
- Disable all database triggers for the Insert Process,
re-enabling the triggers after the process completes.
- Never
- Execute all database triggers during the Insert
Process.
- Prompt
- Display the Disabling Trigger/Constraint
Confirmation dialog, listing tables for each DB Alias with
the corresponding triggers and constraints. You can selectively disable
database triggers during the Insert Process and selectively re-enable
triggers when the process is complete. (For more information, refer
to Disable Triggers and Constraints.)
Disable Constraints
Options
for disabling relational integrity constraints. You can disable relational
integrity constraints for Oracle, Informix,
and SQL Server (Version 7 or later).
- Always
- Disable constraints during the Insert Process, re-enabling
the constraints after the process completes.
- Never
- Retain referential integrity constraints. When this
option is selected, a warning message is displayed during processing.
- Prompt
- Display the Disabling Trigger/Constraint
Confirmation dialog, listing tables for each DB Alias with
the corresponding triggers and constraints. You can selectively disable
database constraints during the Insert Process and selectively re-enable
constraints when the process is complete. (For more information, refer
to Disable Triggers and Constraints.)
Always Call Create
Select
this check box to start the Create Utility before the Insert Process
begins, to allow you to create or drop objects in the destination
database. Clear this check box to start the Create Utility only when
necessary to create desired objects in the destination database.