I'm working on a project that imports data from external data sources
into a firebird (V1.5.2) database.

I found out that the fastest way to do that was to use external
tables.

I put the data to ascii files, create an external table that reads
from that file and I execute the following statement:

INSERT INTO MYTABLE (COLUMN1, COLUMN2, ...)
SELECT NULLIF(COLUMN1,''), NULLIF(COLUMN2,''), ...
FROM MYEXTERNALTABLE

That works fine and realy fast!

The problem is with the columns of type char or varchar. I noticed
that
in MYTABLE all the similar typed columns where right-padded with
spaces
to the full length of each column as declared in the external table.

To solve that I used the rtrim UDF from ib_udf.dll and my statement
became like this:

INSERT INTO MYTABLE (COLUMN1, COLUMN2, ...)
SELECT NULLIF(RTRIM(COLUMN1),''), NULLIF(RTRIM(COLUMN2),''), ...
FROM MYEXTERNALTABLE

The results where the same!! The only difference was that now the
columns where padded to the size of the column in MYTABLE.

I tried the following:

INSERT INTO MYTABLE (COLUMN1, COLUMN2, ...)
SELECT RTRIM(COLUMN1), RTRIM(COLUMN2), ...
FROM MYEXTERNALTABLE

Now the length of the resulted columns were the correct but, as I
expected I had no null values but '' (empty strings)

An other problem is that the rtrim UDF, is limited to CSTRING(255),
something that may not cover all my cases.

Does anyone have an idea how to overcome tha problem?

Thanks in advance!

Michael Apessos