Firebird Interview Question:

Is there some bulk load or other way to import a lot of data fast?

Submitted by: Administrator

Currently there is only one way to quickly load a lot of data into database. That is by using external tables. You should read the manual for details, but here's a short explanation. You create a binary or textual file using the external table format and then hook it up in the database using a statement like this:

CREATE TABLE ext1 EXTERNAL 'c:myfile.txt'
(
field1 char(20),
field2 smallint
);

To do quick import into regular table, do something like this:

INSERT INTO realtable1 (field1, field2)
SELECT field1, field2 FROM ext1;

This insert would still check constraints, foreign keys, fire triggers and build indexes. If you can, it is wise to deactivate indexes and triggers while loading and activate them when done.

Make sure you drop the external table when done, in order to release the lock on the file.

The main problem with external tables is handling of NULLs and BLOBs. If you need to deal with those, you're better off using some tool like FBExport. However, please note that external tables are much faster.
Submitted by: Administrator