Skip to Content
avatar image
Former Member

Load Table inserting too many rows

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,

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 01, 2016 at 04:30 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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!

  • avatar image
    Former Member
    Apr 06, 2016 at 12:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded