Custom Table Loads

The Custom Table Loads profile type is used to download tables and table data from a source database to a target database or text file, allowing for optimized calculation speeds. The order in which records are processed in update mode is based on the user's Profile Scheduler Settings. Contact insightsoftware for additional information about Custom Table Loads.

Create and Maintain a Custom Table Load Job

1. Use the following table to enter data in the Custom Table Loads > Job tab:

   Field	Description
   Job Name	Specify the name of the job.
   Comments	If desired, specify comments related to the job.
   General Information:
   Source Database	Click the drop-down list, and, select the connection to be used for connecting to the source database containing the original tables. The list displays Access, DSN, iSeries, Oracle, or SQL Server connections, or SAP (NetCo 3.0) host configurations to which the user is authorized.
   Target Database	Click the drop-down list, and, select the connection to be used for connecting to the target database, or, select Text Files. The list only displays SQL Server connections flagged as load local connections, and to which the user is authorized.
   Replace Data	If selected, the system deletes records for the selected table(s) prior to downloading data. If not selected, the download process only adds new and/or updates existing records for the selected table(s).
   Text File Options:	These options only appear when Target Database is set to Text Files.
   File Location	Type the path, or, click the Browse button to specify the text file location.
   File Extension	Type the value, or, click the drop-down list, and, select the text file extension (for example, CSV or TXT).
   Delimiter	Type the value, or, click the drop-down list, and, select the delimiter to be used (for example, comma, semicolon, space, or tab).
   Text Qualifier	Click the drop-down list, and, select the text qualifier to be used.  Valid options are: double quote and single quote.
   Include Headings	If selected, headers are included in the text file.

   Note: Excel's CSV file requirements are comma and double quote.

   

2. The Tables to Transfer panel does not initially list any tables. Click the Search for Tables button to open the Tables panel. In the Tables panel, enter the appropriate selection criteria in the Schema/Library and Search For fields, and click Search. The system lists tables matching the search criteria. From the list, select the desired table(s) and click Add. The system returns the select table(s) to the Tables to Transfer tab. Click Close to close the panel.

   
   

3. Use the following table to enter data in the Custom Table Loads > Tables to Transfer tab:

   Field	Description
   Include	If selected, indicates to include the table in the load process.
   Source Name	Displays the schema (if available) and name of the selected source table.
   Target Table Name	Specify the target table name. Defaults to the source table name. A sequence number is appended if duplicate names exist.
   Included Columns	If a table is included, the system defaults to include all columns. If necessary, click the cell, and, in the Define Table Structure panel, select the column(s) to be included when building or inserting data to the target table, then click OK. The system returns the selected column(s) to be included in the target table. Note: columns that are part of the primary key cannot be excluded.
   Primary Key	If a table is included, the system defaults the primary key for the table, when possible. If necessary, click the cell, and, in the Define Table Structure panel, select the desired column(s) to be part of the primary key. If data does not fit with a concept of uniqueness, select the Assign Unique Identifier check box instead, allowing the system to define a unique field to be used for the table. Once the appropriate data has been selected, click OK. The system returns the selected column(s) to be used as the primary key, or returns the *Create Unique Identifier literal.
   Indexed Columns	If necessary, click the cell, and, in the Define Table Structure panel, select the desired column(s) to make up the indexed columns key for the table, then click OK. The system returns the selected column(s) to be used for indexing.
   Criteria	If necessary, click the cell, and, in the Build Select Query panel, enter the selection criteria for creating a small subset of data to be uploaded.

   Button	Function
   Search for Tables	Opens the Tables panel, allowing the user to search for, and return selected tables to the Tables to Transfer panel.

   

4. Use the following table to enter data in the Define Table Structure panel:

   Field	Description
   Assign Unique Identifier	If selected, the system creates a unique field in the target table to be used as the primary key.
   Include	If selected, indicates to include the source column in the target table.
   Source Column Name	Displays the name of the column in the source table.
   Target Column Name	Specify the target column name. Defaults to the source table name.
   Data Type	Click the drop-down list, and, select the data type for the target column. Valid values are: Text Numeric Currency Date/Time Boolean Memo Decimal
   Size	Specify the size of the target column.
   Decimal	Specify the decimal positions of the target column.
   Include in Primary Key	If selected, indicates to use the column as part of the primary key. Note: if data does not fit with a concept of uniqueness, select the Assign Unique Identifier check box instead, allowing the system to define a unique field to be used for the table.
   Index Columns	If selected, indicates to use the column for indexing.
   Incremental Update Control Column	If desired, click the drop-down list, and, select the column to be used for comparison purposes when processing incremental updating.
   Last Incremental Value	If desired, specify the last value processed for the control column. When the job is run in update mode (for example, Replace Data is not selected), the system processes only records exceeding the last incremental value, then updates the last incremental value accordingly.

   

5. See Custom Views for instructions on creating and maintaining user-defined views for defining a subset of data to be uploaded.

   

6. Proceed to the next step: Schedule Job.