Previous Topic

Next Topic

Book Contents

Book Index

Load using Insert Statements

When Load using Insert statements is specified, AQT uses a separate insert statement per row being loaded.

Generate Script

You can write the SQL statements to a script file by checking the Generate script option. The script file used is loadscript.sql in the 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.

The script is generated when you either run the load or use Preview. Using Preview gives you the ability to generate the load script without running the load: you may then wish to save the load script for running at a later time.

If running a large load it is recommended that you do not use Generate Script - this will slow the load.

Use Bulk Inserts

When the Use Bulk Inserts option is specified, AQT will load multiple rows with a single SQL statement.Example:

insert into table (col1, col2, col3) values ('A',1,'a'), ('B',2,'b'), ('C',3,'c'), ('D',4,'d')

You can specify the maximum number of rows that will be inserted per Insert statement (Max Insert Rows).

This provides a faster method than Load using Inserts and can sometimes even be faster that the parameterized method.

Some databases do support this syntax so for these databases this load method cannot be used.

Disadvantage of Bulk Inserts

You need to be aware of the following issue with using this method.

If any value in the Insert statement has an error, the entire Insert statement is rejected by the database and none of the rows in that statement will be loaded.

So, for instance, you are loading 50 rows per Insert statement, any error in those 50 rows will cause all 50 rows to not load.

Consequently this method is most suitable for loading data which is "clean" and likely to load without errors.

Error Limits with Bulk Inserts

The Data Loader willl stop when either the Duplicate Row Limit or Error Limit have been exceeded.

When using the Bulk Insert option, these limits apply to the number of statements which hit this error, rather than the number of rows which did not load.

For example: suppose you are loading 50 rows per Insert statement and the Error Limit is 20. If a single Insert statement fails then 50 rows will not be loaded, however this will only count as 1 towards the Error Limit. The Error Limit will only be hit when 20 Insert statements have hit this error. In this case up to 1000 rows will not have been loaded.

Advanced Query Tool
© 2023 Cardett Associates Ltd. All rights reserved.