Load Table inserting too many rows

[Former Member]

Hello, I am trying to extract and load data on the same IQ 16 server (on Windows). The data extract seems fine, and the load works but it inserts too many rows. I have 2 tables, Dwelling_New with the data, and Dwelling_New_loadtest which is an exact copy but empty for testing the load.

This is my code:

set temporary option Temp_Extract_Size1 = '134217728';

set temporary option Temp_Extract_Binary = 'ON';

set temporary option Temp_Extract_Directory = 'E:\archive\';

set temporary option Temp_Extract_Name1 = 'Dwelling_New.dat';

select * from Dwelling_New;

set temporary option Temp_Extract_Name1 = '';

load table Dwelling_New_loadtest (col1, col2, col3 binary with null byte)

using client file 'E:\\archive\\Dwelling_New.dat'

quotes off

escapes off

format binary;

The load works but inserts 8,943,227 records whereas the original table only has 7,567,346 records.

Why is this happening?

Thanks,

[markmumy · Accepted solution]

Looks like you are missing the "binary with null byte" after each column.  You only have it after col3.  Our binary extract drops a null byte after each column so you need to specify it for each.

load table Dwelling_New_loadtest (col1 binary with null byte, col2 binary with null byte, col3 binary with null byte)

using client file 'E:\\archive\\Dwelling_New.dat'

quotes off

escapes off

format binary;

Also, omit the using client file.  Instead use "using file".  When you do an extract it is on the server.  the USING CLIENT FILE would be a client side load which will be slower.  Assuming that the file is on the server, you want the best performance.

Perhaps it is an issue of the file being on a machine different than the IQ server??

Mark

[Former Member · Accepted solution]

Ah ok, I misunderstood the purpose of "binary with null byte", I thought it should only be specified for columns which allow nulls (in my case, only col3 allows nulls). I put it after every column and it inserted the correct number of rows (I also removed "client", the extract file is on the same server).

Thank you!

[Former Member]

Hello, I am experiencing another issue with Load Table. I am exporting and loading in binary format, the tables contain some varbinary data columns, these columns are being right padded with 0s after the load. e.g. original data: 0x6c, loaded data: 0x6c000000 for a varbinary(4) column. How do I prevent this right-padding?

Thanks