insert_recordset

Article
08/03/2018

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

insert_recordset copies data from one or more tables directly into one resulting destination table on a single server trip. Using insert_recordset is faster than using an array insert. However, array inserts are more flexible if you want to handle the data before you insert it.

insert_recordset is a record set-based operator, which performs operations on multiple records at a time. However, it can fall back to record-by-record operations in many situations.

Syntax

The ListOfFields in the destination table must match the list of fields in the source tables. Data is transferred in the order that it appears in the list of fields. Fields in the destination table that are not present in the list of fields are assigned zero-values as in other areas in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.

insert_recordset DestinationTable ( ListOfFields )

select ListOfFields1 from SourceTable [ where WhereClause ]

[ join ListOfFields2 from JoinedSourceTable

[ where JoinedWhereClause ]]

Example 1: Sub-select that has a Group By

The records, myNum and mySum, are retrieved from the table anotherTable and inserted into the table myTable. The records are grouped according to myNum, and only the myNum records with a value less than or equal to 100 are included in the insertion.

    insert_recordset myTable (myNum, mySum)
        select myNum, sum(myValue) 
            from anotherTable 
            group by myNum 
            where myNum <= 100;

Example 2: Comparing the Performance of Traditional Inserts and Insert_recordset Inserts

By comparing the following two X++ code examples, you can see the performance implications of different designs.

Example 2a: Copy Data for Each Record

The following code example shows the traditional way of copying data.

    ttsBegin;
    while select * from sqlDictionary
        where sqlDictionary.tabId > 0
    {
        bufferDictionary.tabId        = sqlDictionary.tabId;
        bufferDictionary.fieldId      = sqlDictionary.fieldId;
        bufferDictionary.array        = sqlDictionary.array;
        bufferDictionary.name         = sqlDictionary.name;
        bufferDictionary.sqlName      = sqlDictionary.sqlName;
        bufferDictionary.fieldType    = sqlDictionary.fieldType;
        bufferDictionary.strSize      = sqlDictionary.strSize;
        bufferDictionary.shadow       = sqlDictionary.shadow;
        bufferDictionary.rightJustify = sqlDictionary.rightJustify;
        bufferDictionary.nullable     = sqlDictionary.nullable;
        bufferDictionary.flags        = sqlDictionary.flags;
        bufferDictionary.insert();
        countTables++;
        operationProgress.setCount(countTables);
    }
    ttsCommit;

Example 2b: Set Based Operation

The following code example achieves the same outcome as the previous example, but this example is much faster. This example uses the insert_recordset statement.

    void copySQLDictionary2DictionaryLine()
    {
        SqlDictionary sqlDictionary;
        ;
     
        ttsBegin;
        insert_recordset bufferDictionary(tabId, fieldId, array, 
            name, sqlName, fieldType, strSize, shadow, 
            rightJustify, nullable, flags)
        select tabId, fieldId, array, name, sqlName, fieldType, 
            strSize, shadow, rightJustify, nullable, flags 
            from sqlDictionary
            where sqlDictionary.tabId > 0;
        ttsCommit;
    }

Performance Comparison Between 2a and 2b

When copying 14,311 rows, the number of trips to the SQL backend has been reduced from 14,213 to 6. The SQL backend time used has been reduced from 174 seconds to 10 seconds, which is a factor of 17. The overall reduction in time is larger because the new approach uses almost no resources from either the client or the Application Object Server (AOS).

Example 3: Data Assignments from Variables

This X++ code example shows that the insert_recordset statement can insert data that is provided in variables. In this example, the keyword firstonly is used so that only one row is inserted.

Note

Literals, such as 128 or "this literal string", cannot be used as a source of data to be inserted.

    static void InsertVariable3Job(Args _args)
    {
        TableAlphabet    tabA2;
        BankAccountTable tabB3;
        str  1 sLetter = "a";
        str 16 sExampleWord = "apple";
        ;
        DELETE_FROM tabA2;
    
        INSERT_RECORDSET tabA2
            (Letter ,ExampleWord)
        select firstonly
            sLetter ,sExampleWord // Variables.
        from tabB3;
    
        WHILE SELECT * from tabA2
        {
            info(tabA2 .Letter + " , " + tabA2 .ExampleWord);
        }
    /***********  Actual Infolog output
    Message (04:03:52 pm)
    a , apple
    ***********/
    }

Example 4: Joins

The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select. Also, a while select statement with a similar join is shown.

A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or equal to the maximum length of the corresponding database field.

    static void InsertJoin42Job(Args _args)
    {
        GmTabDepartment tabDept2;
        GmTabEmployee tabEmpl3;
        GmTabProject tabProj4;
        GmTabEmployeeProject tabEmplProj5;
        str 64 sDescriptionVariable = "From variable.";
        ;
        DELETE_FROM tabEmplProj5;
    
        INSERT_RECORDSET tabEmplProj5
            (
            Description
            , EmployeeRecId
            , ProjectRecId
            )
        Select
            sDescriptionVariable
            , RecId
        from
            tabEmpl3
            join
                tabDept2
                where tabEmpl3 .DepartmentGuid == tabDept2 .DepartmentGuid
            join RecId
                from tabProj4
                where tabDept2 .DepartmentGuid == tabProj4 .DepartmentGuid
        ;
    
        info(int642str(tabEmplProj5 .rowCount())
            + " ==Number of rows inserted.");
    
        WHILE SELECT *
            from
                tabEmplProj5
                join tabEmpl3
                    where tabEmplProj5 .EmployeeRecId == tabEmpl3 .RecId
                join tabProj4
                    where tabEmplProj5 .ProjectRecId == tabProj4 .RecId
        {
            info(
                tabEmpl3 .EmployeeName
                + "  --works on--  "
                + tabProj4 .ProjectName
                + " (" + tabEmplProj5 .Description + ")."
                );
        }
    /*****************  Actual Infolog output
    Message (01:05:41 pm)
    4 ==Number of rows inserted.
    Alice  --works on--  Project ZZZ (From variable.).
    Alice  --works on--  Project YY (From variable.).
    Beth  --works on--  Project ZZZ (From variable.).
    Beth  --works on--  Project YY (From variable.).
    *****************/
    }

Configuration Key Automation

The system can automatically adjust the behavior of an insert_recordset statement that inserts into fields that have their configuration key turned off.

In the previous example (4) there is an insert_recordset statement for tabEmplProj5. One of the target fields is named Description, and the field's data comes from the local variable sDescriptionVariable. When the configuration key for the Description field is turned off, the insert_recordset still succeeds. The system ignores both the Description field and the variable sDescriptionVariable.