Batch Insert

Batch Insert

• ds.InsertBatch(tableName, tableTypeName, DataTable, InsertOptions)
• ds.InsertBatch(tableName, tableTypeName, DbDataReader, InsertOptions)
• ds.InsertBatch(tableName, tableTypeName, IEnumerable<TObject>, InsertOptions)

Batch inserts fill the gap between individual inserts and bulk inserts. Like bulk inserts, a batch insert allows multiple rows to inserted at one time, which should improve performance. But like individual inserts, you can read back the results to get things such as primary keys.

Audit rules are in effect, so you can still override values such as CreatedDate/CreatedBy.

Examples

Simple insert

In this example, we are using a list of Employee objects to populate the Employee table. Our table type variable is named EmployeeType.

DataSource.InsertBatch("dbo.Employee", "dbo.EmployeeType", employeeList).Execute();

Fetching back the results as objects

This modification of the previous example returns a new list of objects containing the populated employee keys.

var newList = DataSource.InsertBatch("dbo.Employee", "dbo.EmployeeType", employeeList) ToCollection<Employee>().Execute();

Fetching back just the primary keys

If you just want the newly inserted primary keys, you can use this syntax:

var keyList = DataSource.InsertBatch("dbo.Employee", "dbo.EmployeeType", employeeList) ToInt32List().Execute();

Warning:

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

https://msdn.microsoft.com/en-us/library/ms177564.aspx

Streaming

When using the IEnumerable< TObject > option, batch insert supports streaming objects into the database from a lazily initialized source. This has the same limitations as the underlying database for table-value parameters, which means nothing will be actually committed until the operation completes.

Contrast this with bulk insert which can commit records in user-determined batch sizes.

Limitations

For SQL Server, batch inserts require the use of a User Defined Table Type. This table type must have the same columns as those you want to insert into the destination table.

When using the IEnumerable< TObject > option, TObject must have all of the necessary columns to satisfy the User Defined Table Type.

SQL Generation

A single INSERT statement is generated by matching the columns on the table type to those on the destination table. The form is:

INSERT INTO [targetTable] (columnList) SELECT (columns) FROM @TableParameter

The collection itself is loaded into a table-valued parameter. Additional parameters may also be generated to satisfy audit rules.

An OUTPUT clause is added if you want to read back values.