On Ax 2012 R3 one of the new cool features is the ability to insert data from a query directly into a table.
Here is a quick demo of how it works:
I have created a table called TESTQuery2Record. It contains 4 fields: CustGroupId, CustGroupName, PaymTermId and PaymTermDesc. It should be fairly obvious what to expect from these fields.
static void Test_InsertRecordSet(Args _args)
{
TESTQuery2Record testTable;
Map fieldMapping;
Query query;
QueryBuildDataSource qbds_custGroup;
QueryBuildDataSource qbds_paymTerm;
QueryBuildFieldList fldList_custGroup;
QueryBuildFieldList fldList_paymTerm;
// Empty the target test table
// ---------------------------
delete_from testTable;
// Build the query
// ---------------
query = new Query();
qbds_custGroup = query.addDataSource(tableNum(CustGroup));
qbds_paymTerm = qbds_custGroup.addDataSource(tableNum(PaymTerm));
qbds_paymTerm.addLink(fieldNum(CustGroup, PaymTermId), fieldNum(PaymTerm, PaymTermId));
// Field lists are required
// ------------------------
fldList_custGroup = qbds_custGroup.fields();
fldList_custGroup.addField(fieldNum(CustGroup, CustGroup));
fldList_custGroup.addField(fieldNum(CustGroup, Name));
fldList_custGroup.dynamic(QueryFieldListDynamic::No);
fldList_paymTerm = qbds_paymTerm.fields();
fldList_paymTerm.addField(fieldNum(PaymTerm, PaymTermId));
fldList_paymTerm.addField(fieldNum(PaymTerm, Description));
fldList_paymTerm.dynamic(QueryFieldListDynamic::No);
// Specify the mapping between target and source
// ---------------------------------------------
fieldMapping = new Map(Types::String, Types::Container);
fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupId), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, CustGroup)]);
fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupName), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, Name)]);
fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermId), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, PaymTermId)]);
fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermDesc), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, Description)]);
// Let AX handle getting data from the query to the target table
// -------------------------------------------------------------
query::insert_recordset(testTable, fieldMapping, query);
// Done!
// -----
}
The effect of this is x number of records queried and inserted in 1 round-trip to the SQL server and still based on a query.