How to use query::insert_recordset in D365FO

In the realm of performance optimization, the AX query system offers a feature known as query::insert_recordset. This functionality enables the simultaneous insertion of multiple records into a table via a single SQL call, similar to the functionality offered by the insert_recordset command. This will also eliminate the need to loop, as well as roundtrips between the SQL server and the AOS server. Below is a quick demo how query::insert_recordset works:

Query                       query = new Query(queryStr(MDTrvExpenseDetailsQuery));
QueryRun                    queryRun;
QueryBuildDataSource        qbdsTrvExpTrans,
                            qbdsTrvExpTable,
                            qbdsProjTable,
                            qbdsHcmWorker,
                            qbdsDirPerson;
Map                         fieldMapping;
        
HcmPositionWorkerAssignment hcmPositionWorkerAssignment;
HcmPosition                 hcmPosition;

MDTrvExpenseDetailsContract contract = this.parmDataContract() as MDTrvExpenseDetailsContract;
paymentStatus = contract.parmPaymentStatus();

// Prepare the data source fields for record set operation
query.clearAllFields();
qbdsTrvExpTable = query.dataSourceTable(tableNum(TrvExpTable));
qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, ExpNumber));
qbdsTrvExpTable.addSelectionField(fieldNum(TrvExpTable, PaymentStatus));

qbdsTrvExpTrans = query.dataSourceTable(tableNum(TrvExpTrans));
qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, AmountMST));
qbdsTrvExpTrans.addSelectionField(fieldNum(TrvExpTrans, ApprovalStatus));

qbdsProjTable = query.dataSourceTable(tableNum(ProjTable));
qbdsProjTable.addSelectionField(fieldNum(ProjTable, ProjId));
qbdsProjTable.addSelectionField(fieldNum(ProjTable, Name));

qbdsHcmWorker = query.dataSourceTable(tableNum(HcmWorker));
qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, PersonnelNumber));
qbdsHcmWorker.addSelectionField(fieldNum(HcmWorker, RecId));

qbdsDirPerson = query.dataSourceTable(tableNum(DirPerson));
qbdsDirPerson.addSelectionField(fieldNum(DirPerson, Name));

//Add range on the Payment status of travel expenses table
qbdsTrvExpTable.addRange(fieldNum(TrvExpTable, PaymentStatus)).value(queryValue(paymentStatus));

fieldMapping = new Map(Types::String, Types::Container);
//Specify the mapping between target and source
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Amount), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, AmountMST)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ApprovalStatus), [qbdsTrvExpTrans.uniqueId(), fieldStr(TrvExpTrans, ApprovalStatus)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ExpNumber), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, ExpNumber)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PaymentStatus), [qbdsTrvExpTable.uniqueId(), fieldStr(TrvExpTable, PaymentStatus)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjId), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, ProjId)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, ProjName), [qbdsProjTable.uniqueId(), fieldStr(ProjTable, Name)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, PersonnelNumber), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, PersonnelNumber)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Worker), [qbdsHcmWorker.uniqueId(), fieldStr(HcmWorker, RecId)]);
fieldMapping.insert(fieldStr(MDTrvExpenseDetailsTmp, Name), [qbdsDirPerson.uniqueId(), fieldStr(DirPerson, Name)]);

Query::insert_recordset(mdTrvExpenseDetailsTmp, fieldMapping, query);