Give the DDL for both so that we can take a look...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

> 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?