Import Task Set Virtuals

The external sets that an import task uses may have one or more import task set virtuals. These are fields you define in the external set, which do not exist in the file to be imported. You can use them for storing intermediate values during the import, for example, for computing values on the basis of other fields in the external set. Import task set virtuals can only be used within external sets that are not table-based.

The virtual element receives its value from the import SQL statement defined for the particular import task. For each virtual element that you want to use, you must specify the name, data type and length.

NOTE: The performance of USoft Batch can be much improved if you use virtuals rather than constraints to compute values on the basis of external set elements during import tasks.

To take an example, you can use import task set virtuals to improve the performance of foreign key generation for imported child data. Suppose you have an import task in which the import SQL statement needs to join with the database table PERSON to retrieve the newly generated primary key value for the parent record.

If there are many children for each parent, you can improve performance by retrieving the primary key value once for the entire set of children and storing it in a virtual column of the parent external set.

NOTE: In fact, this method is sometimes the best way to handle tables with automatically generated primary keys, as follows.

Suppose you want to import parent records with related child records, and the child records are in a separate file, and related to the parent records through an ID number. If the table has automatically generated primary keys, then USoft Batch will write new primary keys for each imported record, thereby destroying the links between the parent and child records.

If you retrieve the newly generated primary key value for each parent record, and store it in a virtual column, you can then use it for all the child inserts.

You could also use an alternate key (assuming that there is some other column in the child record that uniquely identifies the parent), and fetch the new key for each parent. But the disadvantage is that you need to first put the parent in the database, and then query the database for the key for each child record, and this will not perform as efficiently as the solution using a virtual column.