Multi-row data insert
Glossary Item Box
Introduction
Starting with version 7.12.4, bpm’online supports multi-row data insert. It is available on the Insert class level and is determined by the Values() method.
After calling the Values() method, all subsequent Set() calls fall into a new ColumnsValues instance. A query with several Values() blocks will be built if there is more than one data set in ColumnsValuesCollection.
Example:
new Insert(UserConnection) .Into("Table") .Values() .Set("Column1", Column.Const(1)) .Set("Column2", Column.Const(1)) .Set("Column3", Column.Const(1)) .Values() .Set("Column1", Column.Const(2)) .Set("Column2", Column.Const(2)) .Set("Column3", Column.Const(2)) .Values() .Set("Column1", Column.Const(3)) .Set("Column2", Column.Const(3)) .Set("Column3", Column.Const(3)) .Execute();
As the result, the following SQL inquiry is generated:
--For MSSQL or PostgreSQL INSERT INTO [dbo].[Table] (Column1, Column2, Column3) VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3) -- For Oracle INSERT ALL into Table (column1, column2, column3) values (1, 1, 1) into Table (column1, column2, column3) values (2, 2, 2) into Table (column1, column2, column3) values (3, 3, 3) SELECT * FROM dual
Use specifics
1. MS SQL limits the quantity of parameters to 2100 while using Column.Parameter in the Set() expression.
2. The Insert class cannot independently split a single query into multiple queries if it contains more parameters than necessary. Queries are split by developers themselves. Example:
IEnumerable<IEnumerable<ImportEntity>> GetImportEntitiesChunks(IEnumerable<ImportEntity> entities,
IEnumerable<ImportColumn> keyColumns) {
var entitiesList = entities.ToList();
var columnsList = keyColumns.ToList();
var maxParamsPerChunk = Math.Abs(MaxParametersCountPerQueryChunk / columnsList.Count + 1);
var chunksCount = (int)Math.Ceiling(entitiesList.Count / (double)maxParamsPerChunk);
return entitiesList.SplitOnParts(chunksCount);
}
var entitiesList = GetImportEntitiesChunks(entities, importColumns);
entitiesList.AsParallel().AsOrdered()
.ForAll(entitiesBatch => {
try {
var insertQuery = GetBufferedImportEntityInsertQuery();
foreach (var importEntity in entitiesBatch) {
insertQuery.Values();
SetBufferedImportEntityInsertColumnValues(importEntity, insertQuery,
importColumns);
insertQuery.Set("ImportSessionId", Column.Const(importSessionId));
}
insertQuery.Execute();
} catch (Exception e) {
//...
}
});
3. The Insert() class does not validate that the number of columns and Set() conditions match. For example, if there is a resulting SQL-query:
INSERT INTO [dbo].[Table] (Column1, Column2, Column3) Values (1, 2), (1, 2, 3)
This creates an exception on the database level. The Insert class is not responsible for a detailed validation. Thus, it only depends on developers.
