Bulk Insertion from AOT Query – Dynamics 365

Published by Ali Ahmed Siddiqui at February 14, 2020

Axguru_DYNAMICS365

Bulk Insertion from AOT Query - Dynamics 365

In Dynamics 365, we can use a static method to insert the record directly into the table, make sure that this method requires all the selection fields contains a non-null values otherwise this method will give you an error.

Here is a quick review on how to achieve this without any loops.

The query contains two data sources using inner join "InventTable" and "CustTable".

insert_recordset method requires three arguments.
1. Table buffer in which data needs to be inserted.
2. Map of fields and container for fields and values in that fields
3. Query from which data needs to be pulled.

// method for inserting data into test table with insert recordset
private void insertDataintoTestTable()
{
        Testtable table;
        Query query = new Query(queryStr(TestQuery));
        QueryBuildDataSource qbds;
        QueryBuildFieldList qbdsFldList;
        Map fieldMapping = new Map(Types::String, Types::Container);

        // clearing all fields in query
        query.clearAllFields();

        // getting the Inventtable datasource 
        qbds = query.dataSourceTable(tableNum(InventTable));

        // setting the property for selecting the required fields
        qbdsFldList = qbds.fields();
        qbdsFldList.clearFieldList();
        qbdsFldList.dynamic(QueryFieldListDynamic::No);
       
        // adding the fields to list  
        qbdsFldList.addField(fieldNum(InventTable,ItemId));
        qbdsFldList.addField(fieldNum(InventTable,CostGroupId));
        qbdsFldList.addField(fieldNum(InventTable,BOMUnitId));

        // getting the Custtable datasource 
        qbds = query.dataSourceTable(tableNum(Custtable));

        // setting the property for selecting the required fields
        qbdsFldList = qbds.fields();
        qbdsFldList.clearFieldList();
        qbdsFldList.dynamic(QueryFieldListDynamic::No);

        // adding the fields to list  
        qbdsFldList.addField(fieldNum(Custtable,AccountNum));
        qbdsFldList.addField(fieldNum(Custtable,CustGroup));

        // map the query fields to table fields
        fieldMapping.insert(fieldStr(Testtable,ItemId),[qbds.uniqueId(),fieldStr(InventTable,ItemId)]);
        fieldMapping.insert(fieldStr(Testtable,CostGroupId),[qbds.uniqueId(),fieldStr(InventTable,CostGroupId)]);
        fieldMapping.insert(fieldStr(Testtable,BOMUnitId),[qbds.uniqueId(),fieldStr(InventTable,BOMUnitId)]);
        fieldMapping.insert(fieldStr(Testtable,AccountNum),[qbds.uniqueId(),fieldStr(Custtable,AccountNum)]);
        fieldMapping.insert(fieldStr(Testtable,CustGroup),[qbds.uniqueId(),fieldStr(Custtable,CustGroup)]);
   
        // add ranges to the query
        query.dataSourceTable(tableNum(InventTable)).addRange(fieldNum(InventTable, ItemId)).value('A0001');

        // reqiures three arguments described above
        ttsbegin;
        Query::insert_recordset(table,fieldMapping,query);
        ttscommit;
}

In this manner I have inserted all the records for item "A0001" in TestTable in a single call to database.

Note: This operation is much faster than both of RecordInsertList and while(queryRun.next()) since there so no loop for performing the insert action in the table.

For any further information

Ali Ahmed Siddiqui

Software Engineer with hands on experience on Dynamics 365 and power BI