Skip to main content

Notifications

Announcements

Share Your Journey with Power Pages and Help Shape Its Future!
News and Announcements icon
Dynamics 365 Community / Blogs / Dynamics 365 F & O - Knowledge sharing by B... / SysDa Insert in X++ D365FO ...

SysDa Insert in X++ D365FO Part I

Bharani Preetham Peraka Profile Picture Bharani Preetham Pe... 3,572 Moderator
This article explains about a new API for writing queries which are more faster than normal insert_recordset/update_recordset (my personal experience). The API name is SysDa. In this article we will learn about bulk insert using SysDa. Though the Syntax is bit tricky or not understandable, this actually has some upper hand when comes to speed.

SysDa is any other way to bulk update/insert/delete/select data like normal while select/query classes or set based selects. But I feel this has better improvement in performance. In general, this creates a set based statement in run time and does the process.  

So lets try to write a bulk insert statement using SysDa.

Writing an insert statement using SysDa requires use of SysDaInsertObject.

Requirement:
Lets say I have a requirement to amend a table in such a way I need to change some fields on that parent/main datasource which requires all the child tables like lines data need to be copied so this totally creates a new record with parent and child data.

Here we will concentrate on how we will copy child data. For the article purpose I am keeping table name like TableName and field names like FieldNameA, FieldNameB etc.

In the below code, I'm having a child table with name TableName, where I am copying all the data from that table to create another set of data.

private void processAmend(UniqueId _amendId, UniqueId _actualId)
{
    TableName tableNameAmend, tableNameActual;

    var insertObject = new SysDaInsertObject(tableNameAmend);
    insertObject.fields()
         .add(fieldStr(TableName, FieldNameA))
         .add(fieldStr(TableName, FieldNameB))
         .add(fieldStr(TableName, FieldNameC))
         .add(fieldStr(TableName, FieldNameD));

   var insertQueryObj = new SysDaQueryObject(tableNameActual);

   var s1 = insertQueryObj.projection()
         .AddValue(_actualId)
         .add(fieldStr(TableName, FieldNameB))
         .add(fieldStr(TableName, FieldNameC))
         .add(fieldStr(TableName, FieldNameD)); 

   insertQueryObj.whereClause(new SysDaEqualsExpression(
   new SysDaFieldExpression(tableNameActual, fieldStr(TableName, FieldNameA)),
   new SysDaValueExpression(_actualId)));

   insertObject.query(insertQueryObj);

   var insertStatement = new SysDaInsertStatement();
   
   ttsbegin;
   insertStatement.executeQuery(insertObject);
   ttscommit;
}

Code Explanation:

After creating the 2 buffers, I have created a variable using SysDaInsertObject, which means it specifies the field selection. This creates below statement.

INSERT_RECORDSET tableNameAmend(FieldNameA, FieldNameB, FieldNameC, FieldNameC) SELECT

The SysDaQueryObject and s1 variable helps us in setting the source of fields. This creates below statement.

_actualId, FieldNameB, FieldNameC, FieldNameD from tableNameActual

Then we have a where clause saying that from Actual table buffer pick all the records which matches with _actualId.

Then we will assign the query i.e., insertQueryObj to insert statement which we created using SysDaInsertObject in the start.

Then using SysDaInsertStatement we will create a new variable and call exceuteQuery() which requires the insert statement from start and does the actual insert operation.

We can also add multiple joins using SysDa. We can see all these Set based statements while debug.

This is all about bulk insert operation using SysDa API. Will be back with joins using update operation soon. That's all for now.

Stay Tuned :)
code snippet widget

Comments