insert_recordset [AX 2012]

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 themyNum 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.
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.).
*****************/
}


Happy DAXing
........