In X++, you can insert records into database tables using different techniques. These methods allow you to add data either row-by-row or in bulk.
Insertion Techniques Overview
insertmethod – Inserts a single record at a time.doInsertmethod – Also inserts a single record but bypasses the usual insert logic.insert_recordsetstatement – Efficiently copies multiple records from one or more tables into another in a single database trip.RecordInsertList.insertDatabase– Inserts multiple rows at once without sorting, in a single trip to the database.RecordSortedList.insertDatabase– Similar toRecordInsertList, but allows sorting the data before insertion.
These bulk insert methods reduce the number of database calls, improving performance. However, under certain conditions, set-based operations might revert to inserting one record at a time. (See: “Conversion from set-based to record-by-record operations.”)
insert Method
The insert method is used to add a single row to the table. It generates system fields like RecId, then inserts the buffer’s data into the database.
Example:
CustGroup custGroup;
ttsBegin;
custGroup.CustGroup = '41';
custGroup.insert();
ttsCommit;
doInsert Method
The doInsert method also inserts one row but skips all event logic and validation, including:
Database event handlers (
onInserting,onInserted)Chain-of-command overrides
The
insert()method logic
insert_recordset Statement
The insert_recordset statement is used to bulk-insert records from one or more source tables into a target table in a single server call.
Syntax:
insert_recordset TargetTable (FieldsList)
select FieldsList from SourceTable [where conditions]
[join FieldsList from OtherTable [where conditions]]
Fields in the target and source must be type-compatible.
If fields match in order and type, you can skip the field list.
System fields like
RecIdare handled by the system.
Examples of insert_recordset
1. Inserting aggregated data from another table:
insert_recordset valueSumName (Name, ValueSum)
select Name, sum(Value)
from nameValuePair
group by Name;
2. Inserting from variables:
int id_var = 1;
str name_var = 'Name1';
int value_var = 1;
insert_recordset nameValuePair (Id, Name, Value)
select firstonly id_var, name_var, value_var from custTable;
3. Inserting using joins and variable fields:
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;
Handling DuplicateKeyException During Insert
When inserting data manually (record by record), you can catch a DuplicateKeyException and retry with adjusted logic—useful when working within a transaction.
Example:
ttsBegin;
try
{
destinationTable.DestinationKeyField = sourceTable.SourceKeyField;
destinationTable.insert();
ttsCommit;
}
catch (Exception::DuplicateKeyException)
{
ttsAbort;
retry;
}