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.

Advertisements

9 thoughts on “New feature in AX 2012 R3 – Inserting data in table directly from query

    1. Hi Hugo,

      Thanks for reading my blog. I just went through my example and did not get any errors. If you would like to I could email you a project with my example to maybe compare the two solutions?

      .søren

  1. Hi,

    Is it possible to initiate fields of the target table?

    Let’s say your testTable has another field “Type”, and that type is not mapped with the CustGroup table, is it possible to add a value to this type field in some way?

    1. Not without doing some coding on the table or query that would not be recommendable.
      I would go for an update_recordset afterwards to fix the missing information. That would – in most circumstances – only result in a single roundtrip to the SQL server so performance wise that would probably be ok to do (depending on indexing and so on).

  2. Hi there very nice site!! Guy .. Beautiful .. Amazing .. I will bookmark your web site and take the feeds additionally…I am glad to find numerous useful info right here in the submit, we’d like work out more strategies in this regard, thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s