In this article, learn how to use insert_recordset in D365. The insert_recordset keyword in x++ is used when you need to copy multiple records from existing tables to a destination table. It is extremely fast, because it generates a single SQL statement that is sent to the server one time.
In contrast, developers sometimes write a ‘while select‘ statement to loop through source tables. Then, inside of the loop, they populate a table buffer, and call the insert() method. This is much slower. Because selecting the data and inserting the data each requires its own communication with the server every time through the loop. In this article, I will explain when to use each approach.
How To Insert Data In D365
Before learning how to use insert_recordset 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.
As can be seen, using table buffer variables is a great way for developers to easily insert data into tables in an easy to write way.
Single Call Versus Multiple Calls
Most of the time, calling ‘insert‘ on a table buffer is fine. However, there are other times when it is a lot faster to user insert_recordset in D365 to insert the data. This is because calling insert_recordset only makes a single call to the database, instead of many.
The best way to explain is to look at a few examples.
Multiple Calls Using Insert
In the below example, I created a runnable class (job) to copy values from the MCRSalesStatsCustTable table to the table named MCRCustRFMScore. Name the job ‘tutorial_Insert‘.
The purpose of this is to save off a history of the records in one table for reviewing later.
class tutorial_insert
{
public static void main(Args _args)
{
MCRCustRFMScore mcrCustRFMScore;
MCRSalesStatsCustTable mcrSalesStatsCustTable;
date effdate;
effdate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());
while select mcrSalesStatsCustTable
where mcrSalesStatsCustTable.OrderFrequency > 0
{
mcrCustRFMScore.LastOrderDate = mcrSalesStatsCustTable.StatLastOrderDate;
mcrCustRFMScore.OrderFrequency = mcrSalesStatsCustTable.OrderFrequency;
mcrCustRFMScore.AvgInvoiceAmountMST = mcrSalesStatsCustTable.AvgInvoiceAmountMST;
mcrCustRFMScore.RecencyScore = mcrSalesStatsCustTable.RecencyScore;
mcrCustRFMScore.FrequencyScore = mcrSalesStatsCustTable.FrequencyScore;
mcrCustRFMScore.MonetaryScore = mcrSalesStatsCustTable.MonetaryScore;
mcrCustRFMScore.RFMComposite = mcrSalesStatsCustTable.MCRRFMComposite;
mcrCustRFMScore.EffectiveDate = effdate;
mcrCustRFMScore.CustAccount = mcrSalesStatsCustTable.CustAccount;
mcrCustRFMScore.RFMDefinitionName = "Test";
mcrCustRFMScore.insert();
}
}
}Notice, the code loops through each record in the MCRSalesStatsCustTable, copies some values over to the MCRCustRFMScore table buffer, then calls the ‘insert‘ method.
In this example, every time the insert method is called, the system generates a SQL statement and runs it against the database. The insert method gets called every time the while loop is entered. Which occurs for every record in the MCRSalesStatsCustTable where the order frequency is greater than 0.
Therefore, pretend there are a million records in the MCRSalesStatsCustTable table. In this scenario, the system would have to generate and run one million different SQL statements. Sending data back and forth between the system and the database between each and every run. This would be terribly slow.
Single Call Using Insert_Recordset
Now, let us look at how to use insert_recordset in D365 to create the same data but a whole lot faster.
Create a new runnable job (class), and name it ‘tutorial_Insert_recordset‘.
Next, populate the class with the following code.
class tutorial_Insert_recordset
{
public static void main(Args _args)
{
MCRCustRFMScore mcrCustRFMScore;
MCRSalesStatsCustTable mcrSalesStatsCustTable;
date effdate;
MCRRFMDefinitionName definitionName = "Test";
effdate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());
insert_recordset mcrCustRFMScore
(LastOrderDate, OrderFrequency, AvgInvoiceAmountMST,
RecencyScore, FrequencyScore, MonetaryScore, RFMComposite,
EffectiveDate, CustAccount, RFMDefinitionName)
select StatLastOrderDate, OrderFrequency, AvgInvoiceAmountMST,
RecencyScore, FrequencyScore, MonetaryScore, mcrrfmComposite,
effdate , CustAccount, definitionName from mcrSalesStatsCustTable
where mcrSalesStatsCustTable.OrderFrequency > 0;
}
}Insert Recordset Explained
In order to understand how to write an insert_recordset in D365, we need to understand each of the parts.
- First, we use the syntax “insert_recordset <table buffer variable name>” to tell the system which table we wish to insert data into. This is the destination table.
- Second, we need to tell the system which fields on that table buffer we will populate and in what order. Write “( <field one name>, <field two name>, … )“.
- Thirdly, we need to write an x++ select statement that will retrieve the data from the source table. By using a ‘select’ statement, we no longer need a ‘while loop‘. See the last few lines of the method. For a reminder, read my article on How To Write An X++ Select Statement.
Use A Field List
To clarify, there are a few important things to understand about the select statement.
Unlike most select statements written in x++, this select statement does not use the keyword ‘firstonly‘. Rather, the purpose of this statement is to return all of the records that meet this criteria and not just one record.
Additionally, the select statement must use a ‘field list‘ to specify the name of each field you are retrieving the from source table. Each field you select from the source table will go into the corresponding field in the destination table. Therefore, the order of the ‘field list‘ matter.
Finally, we can see that the insert_recordset in D365 is part of a single line of code. The system, converts this x++ code into a single SQL statement and runs this command on the database.
While the system still needs to copy all of the records from the source table to the destination table, it can do this far more efficiently and faster when it understands the whole job that it needs to accomplish. Whereas, in the other approach, the database retrieves many separate commands.
D365 insert_recordset example
Additionally, if you want to see a real example of an insert_recordset in D365, you can search the source code. Specifically, look at the class MCRRFM. Then, look at the method ‘saveCustomerRFMHistory‘.
In this method, the system does exactly what our example does. It copies data from one table to another using an insert_recordset.
Using Variables
Often times, when writing an insert_recordset in D365, there are static values you need inserted into the destination table that do not exist on the source table. In those scenarios, store the value in a variable, and then reference the variable in the ‘field list‘ of your select statement.
Notice, in our example, we stored the current date into a variable named ‘effDate‘. (This is short for effective date). Then, we used the variable ‘effDate‘ in our select statement. Based on the order we listed this variable, the value will be stored in the ‘EffectiveDate‘ field on the MCRCustRFMScore table.
Likewise, we did this a second time. We stored the word ‘TEST’ in a variable named definitionName. Then, we used this variable in the select statement ‘field list’. Based on the order, the value will be inserted into the field ‘RFMDefinitionName‘ on the MCRCustRFMScore table.
NotExists Join
Lastly, there is one more common trick when writing an insert_recordset in D365 that I wanted to show you. When writing the ‘select’ statement, you can join the source table to the destination table using a ‘notexists’ join.
This approach can be used to only select records from the source table that do not yet exist in the destination table. Then, insert them into the destination table.
This is extremely helpful in making sure you do not insert duplicate records into the destination table.
See the method ‘logEventForMultipleDocuments‘ on the table DocumentEventLog, for an example.
Microsoft Documentation
For further information and examples on how to use insert_recordset in D365 see Microsoft’s documentation.
Additionally, if you are interested, view the documentation on the TSQL ‘insert‘ statement that the system converts the insert_recordset into.
Conclusion
Using an insert_recordset in D365 can dramatically decrease the amount of time it takes the system to insert data in a table. It is especially effective when the code is looping through, and copying, many records. However, there are only specific scenarios where the code can be changed to use the insert_recordset. When there is conditional logic, such as ‘if‘ statement inside the ‘while loop‘, you may not be able to convert the code to use an insert_recordset. You might be able to convert it into a couple insert_recordset statements and still increase performance. You also might be able to use an insertdatabase statement. However, there are some scenarios where the logic requires you to still use a ‘while loop‘.
