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


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 (see FAQ #90 for details).

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.


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous