Tuesday, April 29, 2014

set based operations

insert_recordset: 
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.
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.
Example:
insert_recordset myTable (myNum, mySum)
                 select myNum, sum(myValue)
                 from anotherTable
                 group by myNum
                 where myNum <= 100;
update_recordset: 
The X++ SQL statement update_recordset enables you to update multiple rows in a single trip to the server. 
This means that certain tasks may have improved performance by using the power of the SQL server.

update_recordset resembles delete_from in X++ and to UPDATE SET in SQL. It works on the database server-side on an SQL-style record set, instead of retrieving each record separately by fetching, changing, and updating.
AOT-->Tables-->SlaesTable-->Methods-->updateDeadlineOnline()
delete_from:
You can delete multiple records from a database table by using a delete_from statement.
This can be more efficient and faster than deleting one record at a time by using the xRecord.delete method in a loop.
If you have overridden the delete method, the system interprets the delete_from statement into code that calls the delete method one time for each row that is deleted.
AOT-->Tables-->SlaesTable-->Methods-->deleteAllLines

public static void deleteAllLines(SalesId _salesId)
{
    SalesLine   salesLine;
    ttsbegin;
    delete_from salesLine where salesLine.SalesId == _salesId;
    ttscommit;
}

No comments:

Post a Comment

update_recordset with joins

 update_recordset with joins update_recordSet storeTransfer         setting      TransactionId = transfertable.TransferId     join transfert...