How To Use InsertDatabase In D365

In this article, learn how to use insertDatabase in D365 to insert multiple records into a table faster than calling the insert method on each record. There are several different ways to insert data into tables in Microsoft Dynamics 365 F&O. The most common is to call the insert method on the table buffer. However, there are situations, when inserting many records, where using the RecordInsertList or RecordSortedList class and calling insertDatabase will improve performance. In this article, I will explain how and when to use this approach.

How To Insert Data In D365

Before learning how to use insertDatabase in D365, I recommend you start by reading my article “How to Insert Data In D365“.

To remind you, in x++ the most common approach is to populate table buffer variables with data. Then, call the ‘insert‘ method on that table buffer.

At that point, the system creates a SQL statement that inserts that data into the corresponding table in the database.

The system also sets specific system fields for us automatically, such as RecId and DataAreaId.

In summary, use table buffer variables for a great way to easily insert data into tables in an easy to write way.

Insert Calls The Database For Each Record

When called many times, the insert method can take longer overall than other approaches.

For example, developers often need to write a ‘while select‘ loop and inside of the loop they call the ‘insert‘ method. Therefore, the ‘insert‘ method runs every time through the loop. Consequently, every time insert runs, the system has to generate a sql statement, ask the database to insert the record, and wait on a response back. This results in many round trips to the database.

InsertDatabase Calls The Database Fewer Times

In contrast, there are times when using insertDatabase in D365 will perform better than calling the insert method. Instead of calling insert, each record is first added to a list of records stored in a object of type RecordInsertList or RecordSortedList. This object stores the records in memory. Then, in the code, after all the records are added to this list of records, the call to the insertDatabase method is made.

Technically, the system does not necessarily insert all the records all at once. Instead the documentation says: “Records are inserted only when the kernel finds the time appropriate, but they are inserted no later than the call to the insertDatabase, add, and insertDatabase methods.” The system can “insert more than one record into the database at a time, which reduces communication between the application and the database.”

To make things easier to my mind, I just imagine all the records being inserted when the call to insertDatabase is made, even if that is not strictly true.

Fewer trips to the database and back means this often performs much faster than calling ‘insert‘ which talks to the database for each and every record.

Example

Next, in order to learn how to use insertDatabase in D365, let us look at an example.

This first example comes from Microsoft’s documentation for RecordInsertList found here.

void copyBOM(BOMId _FromBOM, BOMId _ToBOM) 
{ 
    RecordInsertList BOMList; 
    BOM BOM, newBOM; 
    BOMList = new RecordInsertList(tableNum(BOM)); 
    while select BOM 
    where BOM.BOMId == _FromBOM 
    { 
        newBOM.data(BOM); 
        newBOM.BOMId = _ToBOM; 
        BOMList.add(newBOM); 
    } 
    BOMList.insertDatabase(); 
}

Instantiating The RecordInsertList Variable

To explain how to use insertDatabase in D365, there are several steps.

First, declare a variable of type RecordInsertList. In the above example, the variable is named “BOMList“. This object will end up storing a ‘list’ of records to be inserted.

Second, instantiate the RecordInsertList class variable. Pass in the type of table record that will added to this list.

<replace with your variable name> = new RecordInsertList(tableNum(<replace with the name of your table>)

In the above example, the variable named “BomList” is set to instantiated RecordInsertList class object, that is able to store records of type “BOM“. If, for example, you were planning on inserting many customers, you would write this code instead:

RecordInsertList custTableList;
custTableList = new RecordInsertList(tableNum(CustTable);

Populating the RecordInsertList

In the third step of using insertDatabase in D365, populate a table buffer variable with data, and add that table buffer variable to the RecordInsertList object, instead of calling ‘insert’. In the above example, the ‘while select’ loops through the BOM table, and populates the ‘newBOM’ table buffer variable with data.

Oftentimes, you will see the code “newBOM.insert();” But instead, in this example you see:

BOMList.add(newBOM); 

This takes the populates table buffer variable and adds it to the RecordInsertList object variable. Each time through the loop, another record is added to this list of records. This data is stored in memory for the time being.

Inserting The Records

Finally, call insertDatabase in D365 on the RecordInsertList object variable to insert the records into the database. In the above example, the code looks like this:

BOMList.insertDatabase();

Similarly, in your case, replace “BOMList” with the name of your RecordInsertList variable.

The system generates the SQL statement and asks the database to insert the database. Since this results in fewer trips to the database, this often performs much faster than communication for each record.

When To Use InsertDatabase

Now that you know how to use insertDatabase in D365, it is important to understand when to use it.

As discussed, you could call the insert method, instead of using a RecordInsertList and calling insertDatabase. So when should you consider using this approach? Let us talk through each approach.

Insert

First, call ‘insert‘ when you are only inserting a single record or relatively few records. Most developers use the ‘insert’ method initially. Then, change to one of the next couple approaches when they need the code to run faster. Ideally, more code should use one of these next two approaches.

Insert_recordset

Second, consider using ‘insert_recordset’ when you are reading values from tables, and copying them into another table. See my article on how to use insert_recordset in D365. If you can write a SQL statement to retrieve all your source information this is a great approach.

InsertDatabase

Thirdly, use insertDatabase when the data you are inserting does not come from a table. Such as when you are reading from a file. Or, when processing data a user entered on a form not tied to a datasource.

Additionally, you cannot use insert_recordset when you need to call code to populate the record you are inserting. Consider this example from the SalesTable table ‘createRetailSalesAffiliation‘ method.

The system calls the ‘initValue‘ method on the retailSalesAffiliationNew table buffer variable. Developers use the ‘initValue‘ method to set default values on the record. While a developer can set fields explicitly, this takes advantage of object oriented practices.

Also, use insertDatabase when code inside a ‘while loop‘ has conditional ‘if‘ statements. This is because a insert_recordset cannot typically be used in this scenario.

Essentially, any time you have a ‘while loop‘ an insertDatabase likely can be used. However, when only a few records will be inserted, it is likely not worthwhile to call insertDatabase. Use ‘insert‘ instead.

RecordSortedList

Furthermore, there is a a class called RecordSortedList. It works very similarly to RecordInsertList. However, it will automatically sort the records that you add to it, given a sort order you provide.

See Microsoft’s documentation here.

Here is an example from the SalesTable table, copyDiscountLines method.

From my experience, most of the time, there is no need to insert records in a particular order. Therefore, using RecordInsertList is more common.

Conclusion

While the ‘insert‘ method can be used in each of these example, it is best to use code that will insert records the fastest. Code such as insert_recordset and insertDatabase. Now that you have learned how to use insertDatabase in D365, you will be able to use this technique to increase the performance of your code. It is very satisfying when you find that a process that used to take minutes or longer, now takes seconds using when you re-write it to use one of these better performing approaches!

Peter Ramer

Peter Ramer is a part of the Managed Application Services team at RSM working on Microsoft Dynamics 365. He focuses on the Retail and Commerce industries. When he is not solving problems and finding ways to accelerate his clients' business, he enjoys time with his three kids and amazing wife.