|
|
|
|
|
The Data Loader has three different modes of operating. These are specified on the Options Tab > Load Method > Mode
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.
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.
By default, AQT loads the data by using a parameterised insert statement, eg:
insert into table (col1 col2, col3, col4) values (?,?,?,?)
This statement is sent to the database ("prepared"). For every row loaded:
This provides a very fast way of loading the data. However some databases have trouble with the use of parameter markers. To deal with this, AQT provides an alternate method for loading data. When Load using Insert statements is selected, AQT will not use the parameter-marker method, instead will run a series of normal insert statements. This is a slower method for loading data, however is more reliable for some databases.
When you select this method, you have the option Generate script; when this is specified AQT will write the insert statements to a script file. You might do this if you wish to run the load script later. The load script is generated when you either Load the data, or do a Preview. The script will be written to file loadscript.sql in your default directory. Once a script has been created you will get a button Show Script at the bottom of the window. Clicking on this will show you the script file.
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.
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.
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.