The Batch Insert option gives you the ability to insert multiple rows of data into a single database table when a triggered event occurs. Using Batch Insert you can:
- Insert entire arrays in one transaction rather than one insert per array element.
- Improve database performance. Most database systems offer increased performance for batch insert operations rather than individual inserts.
The following assumes that you have specified the database transport and selected a schema and table.
- Click the Action down arrow, and
then select Batch Insert.
The BatchCount box and the BatchInsert tab become available.
The BatchInsert tab is populated with the column information for the selected table. This is the table that will be used for the target of this batch insert.
- Type a numeric value in the Batch Count box. For this example, 5. The
value indicates the number of inserts to batch. For
example, if the size of an array equals 8, and you
specify 5 in the Batch Count box, only the first 5 elements
in the array will be used.
The next step is to create the map variables. You can automatically create the map variables using the MapTable button. When you use Map Table, the value typed in the BatchCount box is automatically added to the Count column of the Input tab.
If you add each map variable separately, the value of the Count parameter (for the map variable) must match the value in the BatchCount box; otherwise, an error will occur. For more information, see Creating map variables automatically and Creating each map variable separately.
Batch Insert and the Trigger window
When a transport map that contains a batch insert
operation is added as a trigger action, a special
batchCount variable appears on the
Input tab of the Trigger window.
At runtime, the batchCount variable is used to control how many array elements to send as one batch. You can map the batchCount variable to a device variable or local variable; and whenever the value of the device variable or local variable changes, the batch insert operation batches whatever the number is of the array element. This accommodates a scenario whereby each time the trigger executes, the number of array elements to batch for inserts changes. Otherwise, it will default to the number specified in the transport map (BatchCount box) and will always be the same number for each run.
It is important to know that the value for
batchCount cannot be set to a value
greater than the value in the Count column
for the array variables.
Also, if you set the value of batchCount
to 1, the batch insert operation will behave like a regular
insert operation. In other words, one row is batched for
insert (a single insert statement is performed).