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.