insert_recordset x++

insert recordset in X++, also called insert_recordset x++ language is a set-based record inserting command that lets you copy multiple rows from one or more source tables into a destination table in just a single round trip to the database. This makes it far more efficient than inserting records one at a time, because the entire batch is pushed to SQL Server as one consolidated operation.

A typical pattern looks like this:

insert_recordset TargetTable(FieldX, FieldY, FieldZ)
    select SourceField1, SourceField2, SourceField3
    from SourceTable
    where SourceTable.StatusId == 2;

In this structure, the insert_recordset keyword starts the operation, while the destination buffer and fields define where the data should land. The select statement provides the values from the source, and optional joins or filters can refine which records are pulled in.

Another example:

static void Example_insert_recordset(Args _args)
{
    insert_recordset SalesArchive (SalesId, CustAccount, Amount)
        select SalesId, CustAccount, LineAmount
        from SalesLine
        where SalesLine.DlvDate < today();
}

Here, old sales lines are moved into an archive table in one efficient step, showing how bulk inserts are often used in real-world data management.

The main benefits are clear: performance and efficiency. Since it generates a single SQL INSERT statement, it cuts down on server communication and speeds up large data transfers. It is most useful when you need to copy big datasets or run set-based operations, making it a best practice along with update_recordset and delete_from.

It’s worth noting that the RecordInsertList class provides a similar outcome but through a programmatic approach, letting you add records to a list and then flush them to the database at once. This reduces communication between the application and the database but still gives you control over row-level construction, unlike insert_recordset which is purely set-based.

For even more flexibility, the Query class in X++ can define complex data sources, joins, ranges, and sorting, and then serve as input to bulk operations. This allows you to combine dynamic query logic with the speed of a single insert_recordset.

For developers wondering how to insert data in D365 using x++, insert_recordset remains the most efficient option, balancing speed and simplicity when bulk-loading data into a table.