insert_recordset,update_recordset,delete_from in 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

Ex : ledgertranslistreport

helper class

insert_recordset processingStagingTable

        (

            Visibility,

            GeneralJournalAccountEntry,

            Txt,

            CurrencyCode,

            PostingType,

            TransactionCurrencyAmountDebit,

            AccountingCurrencyAmountDebit,

            ReportingCurrencyAmountDebit,

            IsCredit,

            GeneralJournalEntry,

            TransDate,

            JournalNumber,

            Voucher,

            AcknowledgementDate,

            AccountNum,

            AccountName,

            Dimension,

            MainAccount,

            GeneralJournalEntryDataArea)

        select showAcknowledgementDate,

               RecId,

               Text,

               TransactionCurrencyCode,

               PostingType,

               TransactionCurrencyAmount,

               AccountingCurrencyAmount,

               ReportingCurrencyAmount,

               IsCredit from generalJournalAccountEntry

        join RecId,

             AccountingDate,

             JournalNumber,

             SubledgerVoucher,

             AcknowledgementDate from generalJournalEntry

                where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry

                    && generalJournalEntry.AccountingDate >= _fromDate

                    && generalJournalEntry.AccountingDate <= _toDate

                    && generalJournalEntry.Ledger == Ledger::current()

        join MainAccount,

             Name,

             FullyQualifiedAccount,

             MainAccountRecId from mainAccountLedgerDimensionView

                where generalJournalAccountEntry.LedgerDimension == mainAccountLedgerDimensionView.LedgerDimensionId

        join ledger

                where ledger.RecId == generalJournalEntry.Ledger

        join DataArea from companyInfo

                where companyInfo.RecId == ledger.PrimaryForLegalEntity;

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  ]]

Ex : 

static void Raj_Insert_Recordset(Args _args)

{

    CustTable               custTable;  //Standard table

    Insert_Recordsettmp     table;      //myTable

    

    insert_recordset table(AccountNum) 

        select AccountNum from custTable;

        

}

update_recordset: 

Allows you to update multiple rows in a table on a single database trip.

ü  The update_recordset command allows the manipulation of many records in one operation. This command speeds up database processing because of fewer calls to the database. Review the update function using the update_recordset command.

Ex:

SalesTable salesTable;

update_recordset salesTable

setting salesName ="New Enterprises"

where salesTable.custAccount =="2001";

Note : You do not have to use the ttsbegin; or ttscommit; when you use the update_recordset command, however it is recommended to consider using a transaction when making other updates to the database at the same time.

update_recordSet processingStagingTable
                    setting HM_CustVendAccount = cust.AccountNum,
                            HM_CustVendName    = dirPartyTable.Name
                        join AccountNum from cust
                            where processingStagingTable.Voucher == cust.Voucher
                                && cust.TransType != ledgertransType::ExchAdjustment
                        join custTable
                            where custTable.AccountNum == cust.AccountNum
                        join Name from dirPartyTable
                            where dirPartyTable.RecId == custTable.Party;



updating customer name using recordset

update_recordSet customersVendors setting

        Name = dirParty.name

            join custVendTable

                where customersVendors.AccountNum == custVendTable.AccountNum

                    join dirParty

                        where custVendTable.Party == dirParty.RecId;

delete_from 

                      The delete_from command removes multiple records from the database at one time. Similar to the update_recordset command, delete_from consolidates many database calls into one operation, and increases database performance.

Ex:

 CustTable custTable;

delete_from custTable where custTable.Currency == "ABC";