When to use set-based statements and row-based operations
Set-based statements are used to update, insert, or delete multiple records of data from a table.
Row-based, or non-set-based, operations are used to update, insert, or delete a single record from a table.
Using set-based queries can greatly increase performance by reducing the number of calls that are made to the database.
A row-based operation requires a call to the database for each record that needs to be manipulated, while a set-based statement only calls the database once.
For example, if you need to update 100 records on a table, a row-based
operation would call the update method and call the database 100 times
to update all the records. A set-based statement would call the update_recordset method once and call the database once to update all the records.
Three examples of set-based statements that can be used are:
Update_recordset- Used to update multiple records at once.Delete_from- Used to delete multiple records at once.Insert_recordset- Used to copy multiple records from one or more tables into another table.
The update_recordset can update multiple fields on a
table. You can also specify a where clause to determine what records are
updated. The following is an example of an update_recordset where
you declare the table and call the update_recordset to
update the customer PaymMode and Currency fields only
for the customers with a CustGroup field that equals "US."
Keep in mind that if the update method is overridden, the
update_recordset will call the update method one at a
time instead of all at once.
CustTable custTable;
Update_recordset custTable
Setting
PaymMode = 'Check',
Currency = 'USD'
Where custTable.CustGroup == 'US';
The delete_from method is like the update_recordset. You can delete multiple records at the same time from a table, and you can use a where clause to determine which records to delete. In the following example, the table is declared and then the delete_from is called to delete all the records where the customer's CustGroup field equals "US."
CustTable custTable;
Delete_from custTable
Where custTable.CustGroup == 'US';
Insert_recordset lets you insert multiple records
from one table into another table. You can use a where clause to determine which records to insert, and you can use variables to insert. In the following example, the table that will be inserted into and the table that you are pulling data from are declared. The insert_recordset is then called to insert data into the fields that are listed in parentheses. The fields from the staging table are then called in the order that the fields were listed in the parentheses. Now, those fields will be inserted into the CustTable where the staged customer's CustGroup field equals "US."
CustTable custTable;
CustCustomerBaseStaging custCustomerBaseStaging;
Insert_recordset custTable (accountNum, custGroup, paymMode, currency)
Select CustomerAccount, CustomerGroupId, PaymentMethod,SalesCurrencyCode
From custCustomerBaseStaging
Where custCustomerBaseStaging.CustGroupID == 'US';