IQ REPLACE LOAD

[former_member191072]

Hello Guys,

I want to load from CSV file to IQ16 table , but this table has primary key and CSV has existing primary key data.

I want to take this existing data as update , new data as insert in IQ Load command. But It seems to me, IQ Loader doesn't have this feature .

I am thinking another method ... ie) Load to another table and use insert-select command and update command .

However , I think some guys has same trouble in IQ. How to solve this case ? Does anyone know good method ?

And I googled about this, "Sybase ETL" has this feature for IQ. But this products is obsoleted? Data Services has same feature ?

Rgs,

Jim

[tayeb_hadjou · Accepted solution]

Sorry, the options mentioned above are to record the duplicates. They don't allow "REPLACE".

To my knowledge this feature "Replace the duplicate with the new one" is not part of load options yet.

The methof below could help to workaround :

1-Export data of the existing table to old_data.csv file

2-truncate the table

3-load table from the new csv file

4-load table from the old_data.csv with options IGNORE CONSTRAINT UNIQUE , MESSAGE LOG ROW LOG.

Boh new data and old non-duplicated will be then inserted,  and old duplicates will be rejected.

Hope this helps.

Regards,

Tayeb.

[markmumy · Accepted solution]

A lot of times we see this process followed to do 'in place' updates.  In fact, this is what we tend to follow in the Replication Server RTL world when moving updates into IQ:

1. Load new data into a temp table
2. Join the temp and main table and delete from the main table based on a primary key join (delete duplicate rows)
3. Copy the contents of the temp table into the main table using INSERT SELECT

You could also also slightly change the process and only load the primary key into the temp table:

1. Load just the primary key columns of the new data into a temp table
2. Join the temp and main table and delete from the main table based on a primary key join (delete duplicate rows)
3. Use LOAD TABLE to load the raw data file(s) into the main table

Mark

[tayeb_hadjou]

Hi Jim,

To load only the new rows and reject the duplicates, try load options

IGNORE CONSTRAINT UNIQUE , MESSAGE LOG ROW LOG.

Eg.

Load table (....

)

....

IGNORE CONSTRAINT UNIQUE 0

MESSAGE LOG 'c:\\client-data\\load.trace'
ROW LOG 'c:\\client-data\\load_rows.trace' ONLY LOG UNIQUE

see details regarding "MESSAGE LOG ROW log" Load options in Load table statement

LOAD TABLE Statement

Regards,

Tayeb.