Bulk import details

Bulk import is a faster method of importing large quantities of data, for example in the region of hundreds of thousands of records. You can also import from XML.

Bulk import:

1. Uses the BULK INSERT statement to import into temporary tables.

   Note: You do not need to change the database recovery model to BULK LOGGED because bulk import uses bulk operations on temporary tables, and bulk operations on objects in tempdb are already BULK LOGGED.

2. Creates temporary files in the Bulk Import data files folder that is defined as part of the server configuration.

3. Transfers the data into the iBase database and on successful completion of this phase, deletes the temporary files that are created in the Bulk Import data files folder.

Bulk import can be used with:

• Import specifications run manually from iBase Designer (they can be set up and verified in iBase)

• Import batch specifications run manually from iBase Designer (they can also be set up in iBase)

• Import batch specifications are run on a scheduled basis by iBase Scheduler

For each entity or link type imported, bulk import runs through four phases:

1. It copies the import data to a temporary file in the specified Bulk Import data files folder.

   Warning: Secure this folder to prevent unauthorized access to the temporary copy of the import data. This temporary file cannot be deleted if iBase or Scheduler crash during a bulk import.

   The import fails if any errors are encountered while creating the temporary file. However, carrying out a preliminary verification in iBase would identify these errors in advance of the import. The possible errors include:

   • Data that is too long for the iBase field

   • Mismatch between locales (for example dates that are in the wrong format for the locale)

   • Too many matches because the identifiers are not sufficiently selective

   Before running a bulk import that uses a new import specification, you can verify it with some representative sample data. To verify an import, click Verify on the last page of the Import wizard.

2. It imports the data into temporary tables.

   The import fails if any errors are encountered at this stage.  

3. For each entity and link type, it reports any records with any of the following errors:

   • Value not defined in list for field (for 'selected from code list' fields)

   • Mandatory field value not supplied

   • Field value too long

   • Invalid direction

   • Invalid strength

   • Invalid date

   • Invalid time

   • Invalid numbers

   • Incorrect number of columns

   Bulk import then maps data to records in the database to obtain statistics on the numbers of records added, updated, and unchanged when the data is imported. All work takes place in temporary tables; no changes are made to the iBase database at this stage.

   Any errors that occur at this stage prevent individual records for this entity or link type from being created or updated.

   Note: Users can decide on the threshold for the number of errors that will cause an import to stop completely in phases 3 or 4. If unspecified, this is set to a default threshold of 10,000 errors. Records imported into the database before the threshold is reached will remain in the database.

   For example, suppose that you are importing links, and the 'entity 1' records are already imported. During the import of the 'entity 2' records, the error threshold is reached. This means that the 'entity 1' records will remain in the database, but no records for 'entity 2' or the links would be imported.

   (Standard imports will continue to run even if 10,000 errors occur.)

4. Finally, it transfers the data into the database and updates the corresponding import set. Because the data transfer and the update of the import set occur in the same step, the import set will accurately reflect the data that was imported (even after a catastrophic server failure, such as a power cut). It will also delete the temporary files created in the Bulk Import Data Files folder.

   If you are using audit level 4 or 5, the start and end of the import is recorded in the audit log.