AX 2012 R3 – INSERTING DATA IN TABLE DIRECTLY FROM QUERY

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.