Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

insert_recordset

insert_recordset copies data from one or more tables directly into one resulting destination table on a single server trip. It is faster than using an array insert. However, array inserts are more flexible if you want to manipulate 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 a number of situations.

insert_recordset DestinationTable ( ListOfFields )

select ListOfFields1 from SourceTable [ where WhereClause ]

[ join ListOfFields2 from JoinedSourceTable [ where JoinedWhereClause ]]

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 places in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.

Example 1

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

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.

Example 2

insert_recordset tableA (StringA, IntA)
    select StringB, IntB from tableB
        where tableB.IntB >= 9;

The fields StringB, and IntB (when it has a value that is greater than or equal to 9) are retrieved from the table tableB, and inserted in tableA into the fields StringA and IntB, respectively.

Example Comparing the Performance of Traditional Inserts and Insert_recordset Inserts

The following 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;

The following is the same example but uses insert_recordset.

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:

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—a factor of 17. The overall reduction in time is larger because the new approach does not use any client (or AOS) resources.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.