Firebird: bulk insert and bulk export

| category: Programming | author: st
Tags:

Firebird doesn't support INSERT BULK command or bcp.exe utility. But the external tables can be a good replacement. Simple example is below.

Check that firebird.conf file allow to create external tables. Write or uncomment line like

ExternalFileAccess = Full

Create external table

CREATE TABLE BCP_DATA EXTERNAL FILE 'с:\temp\temp.txt' (
  ID CHAR(10) NOT NULL,
  NAME CHAR(100),
  CRLF CHAR(2)  -- CHAR(1) for Linux
);
COMMIT;

Bulk export from some Firebird table (ensure that temp.txt file is empty):

INSERT INTO BCP_DATA(ID, NAME, CRLF)
SELECT ID, NAME, ascii_char(13) || ascii_char(10) FROM MY_REAL_TABLE;

As result, you have temp.txt file in "fixed length columns" format. Bulk import (ensure that temp.txt has data in fixed length format):

INSERT INTO MY_REAL_TABLE(ID, NAME)
SELECT ID, NAME FROM BCP_DATA;

blog comments powered by Disqus