Load Mode

The Data Loader has three different modes of operating. These are specified on the Options Tab > Load Method > Mode

Load. New rows are added to the table. If a row already exists with the given key, the row is not loaded or changed in the table.
Load-Update. New rows are added to the table. If a row already exists with the given key, the existing row in the table is updated with the new values.
Update. The Data Loader does not add any new rows to the table. Instead, the existing rows in the table are updated with the values from the load source.

Load-Update is useful for applying new or changed rows to a table. Note however that it should not be used to resync the contents of two tables, as load-update will not remove deleted-rows. To resync the contents of two tables, use the Resync option of the Data Compare tool.

Update mode is useful for populating a column of a table from another table, a query or a file. This is a problem that DBAs and developers are often faced with, and can be difficult to do by other methods.

Key (for Updating)

If you have specified a Mode of either Load-Update or Update, AQT needs to know the unique key of the target table. AQT will get this automatically if your table has a primary key, unique key, or user-defined key. Otherwise you will need to specify this.

If using Load-Update, your target table must have a primary or unique key defined. AQT first tries to insert a row; when this insert fails it then tries to update. If the table does not have primary or unique key, the insert will not fail, instead you will get duplicate rows loaded.

Update using Update statements

This is a similar option to Load using Insert statements, and applies to how AQT will run the updates.

During testing of the Data Loader, we have found that some databases have trouble when there are two parameterised queries open at the same time. This is particularly true when LOBs are being loaded / updated. If you are having trouble running the Data Loader in Load-Update mode, you could try selecting the Update using Update statements option.

Foreign Key violation

When loading data into a table, AQT checks the response from the Insert for the "row already exists" error code. In Load-Update mode, AQT will then try to update the row. However some databases give the same error code for both Foreign Key violation as they do for the "row already exists" error code (Primary Key violation). In other words, the reason the data cannot be inserted is because the data does not satisfy a Foreign Key constraint, not because the row already exists in the table. When AQT tries to update the existing row in the table, it will not be found. This will be reported as an error in the Data Loader.

Use IDENTITY_INSERT

This option is used when you are using Sybase or SQL Server, and your table has an identity column. Normally, data cannot be loaded into an identity column. If you wish to load data into the identity column, check this option. AQT will set the IDENTITY_INSERT option before loading the table, allowing identity columns to be loaded.

If you do not wish to load values into the identity column (but have it generate a new sequence), de-select the column on the Mapping tab.