HOWTO: SAP Sybase IQ Loading Tables with AutoIncrement/Identity with Zero as a value SOLVED

If the source table has an identity or autoincrement field AND the value starts at zero “0” then load table either server -> server or from file will not work.  IQ is hard coded to reject the rows with the zero value for identity/autoincrement using load table.  Creating a staging table with a numeric field instead of identity/autoincrement will not work with the export file because the export file has the field flagged as an identity/autoincrement field.
This isn’t documented anywhere.
1. Extract out the source table
2. Create a staging table using the same DDL as the destination table, swapping the identity/autoincrement field with numeric(10,0)
3. Load the table into the staging table
4. Turn on identity_insert for the destination table

Set temporary option identity_insert= 'destination_table';

5. Insert into destination table from staging table

insert into destination_table select * from staging_table;

6. Turn off identity_insert for the destination table

Set temporary option identity_insert = '';

7. Drop staging table
8. Rebuild any views that are invalid
FW Howto: List Invalid Views and fix the views in SAP Sybase IQ

Leave a Reply

Your email address will not be published. Required fields are marked *