BulkInsert in a memory optimized table

[Jaap-van-Hengstum]

I'm using a memory optimized table in SQL Server.

When I perform a BulkInsert in that table, it seems to work for large(r) datasets, but on a small dataset I get the following error message:

The operation 'MERGE' is not supported with memory optimized tables.

I assume the BulkInsert implementation contains an optimized version for smaller datasets that uses the MERGE operation. Is there a way to disable that?

[JonathanMagnan]

Hello @Jaap-van-Hengstum ,

There is 3 way our library perform insert:

• Using a derived table when the number of rows is 10 or less
• Using a temporary table when the number of rows is 10 or more and something needs to be returned such as the identity value (or any other configuration that doesn't allow to directly use SqlBulkCopy)
• Using SqlBulkCopy

In your case, the SqlBulkCopy is used with a large dataset (11 rows or more) but the derived table with the Merge statement is used with a small dataset.

One way to always perform a SqlBulkCopy in this case is setting the options TemporaryTableMinRecord to 1. In this case, since a SqlBulkCopy can be used, this resolution will be used instead of the temporary table.

context.BulkInsert(list, options => options.TemporaryTableMinRecord = 1);

Be careful, that means only the BulkInsert will work in our library when performing an operation on the memory tables.

Let me know if that work and if that explanation about this issue is clear.

Best Regards,

Jonathan

[Jaap-van-Hengstum]

I can confirm that setting TemporaryTableMinRecord to 1 allows BulkInsert to work with memory optimized tables if the dataset contains less than 11 records.

Thanks for the explanation.