New feature in AX 2012 R3 – Inserting data in table directly from query

A new version/release of Dynamics AX is finally gone RTM and one of the new cool features is the ability to insert data from a query directly into a table. And why is that so clever you might think? Well, consider the performance boost of not having to loop through thousands of records including the roundtrips between SQL server, AOS server, client and back. That is why this is so interesting.

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. 🙂

The purpose of the demo scenario is to get customer group data including the corresponding payment term information inserted in our test table. To do this I have written this job:

 

static void DEMO_Query_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!
    // -----
}

So what is happening when we execute the above. Besides the delete_from we will see to queries executed on the SQL server.

1. Execution of the query and adding the data to a temporary table:

SELECT T1.CUSTGROUP AS f1,
       T1.NAME AS f2,
       T2.PAYMTERMID AS f3,
       T2.DESCRIPTION AS f4,
       N'usmf' AS DATAAREAID,
       1 AS RECVERSION,
       5637144576 AS PARTITION,
       IDENTITY(BIGINT, 1, 1) AS RECID
INTO [##ax_tmp_usmf8_113_103617]
FROM CUSTGROUP T1
CROSS JOIN PAYMTERM T2
WHERE (
        (T1.PARTITION = @P1)
    AND (T1.DATAAREAID = @P2)
      )
    AND (
    (
        (T2.PARTITION = @P3)
    AND (T2.DATAAREAID = @P4)
    )
    AND (T1.PAYMTERMID = T2.PAYMTERMID)
      )
ORDER BY T1.CUSTGROUP

2. Inserting the data from the temporary table in the target table

INSERT INTO TESTQUERY2RECORD (
    CUSTGROUPID,
    CUSTGROUPNAME,
    PAYMTERMID,
    PAYMTERMDESC,
    DATAAREAID,
    RECVERSION,
    PARTITION,
    RECID
 )
SELECT F1,
       F2,
       F3,
       F4,
       DATAAREAID,
       RECVERSION,
       PARTITION,
       RECID + 5637144596
FROM [##ax_tmp_usmf8_113_103617]

The effect of this is x number of records queried and inserted in 1 roundtrip to the SQL server and still based on a query.