Home / Questions / How to insert data from a query directly into a table in X++ Language
Explanatory Question

How to insert data from a query directly into a table in X++ Language

👁 2 Views
📘 Detailed Answer
🕒 Easy to Read
Read the answer carefully and go through the related questions on the right side to improve your understanding of this topic.

Answer with Explanation

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.