Axguru_blog2
Bulk Insertion from AOT Query – Dynamics 365
February 14, 2020
Attentive business team working on laptops in the office
Address Fast Tab using Table map – Dynamics 365
February 19, 2020

Bulk Insertion with null values in Tables – Dynamics 365

AXGuru D365 F&O

AXGuru D365 F&O

Bulk Insertion with null values in Tables - Dynamics 365

Bulk Insertion with null values in Tables - Dynamics 365

Insert_recordset does not accept null values in the selection fields.

Error: Cannot insert multiple records. The SQL database has issued an error. Cannot insert the value NULL into column ‘ ‘, table ‘ ‘; column does not allow nulls. INSERT fails.
There are some work around which you can do to overcome this problem.

Method 1: This method uses two database calls

You can simply insert all the fields which contains non-null values in the table then update the records in the table using update_recordset. This method will perform two database calls first one for insert and then update.
// method for inserting and updating data into test table with insert recordset and update recordset
private void insertUpdateDatainTestTable()
{
        Testtable table;
        BOM bom;
        Query query = new Query(queryStr(TestQuery));
        QueryBuildDataSource qbds;
        QueryBuildFieldList qbdsFldList;
        Map fieldMapping = new Map(Types::String, Types::Container);

        // clearing all fields in query
        query.clearAllFields();

        // getting the Inventtable datasource 
        qbds = query.dataSourceTable(tableNum(InventTable));

        // setting the property for selecting the required fields
        qbdsFldList = qbds.fields();
        qbdsFldList.clearFieldList();
        qbdsFldList.dynamic(QueryFieldListDynamic::No);   

        // adding the fields to list  
        qbdsFldList.addField(fieldNum(InventTable,ItemId));      

        // map the query fields to table fields
        fieldMapping.insert(fieldStr(Testtable,ItemId),[qbds.uniqueId(),fieldStr(InventTable,ItemId)]);         

        // add ranges to the query
        query.dataSourceTable(tableNum(InventTable)).addRange(fieldNum(InventTable, ItemId)).value('A0001');

        // reqiures three arguments described above
        ttsbegin;
        Query::insert_recordset(table,fieldMapping,query);
        ttscommit;

        // after that use the update_recordset since both these fields can contains null values
        udpate_recordset table
           setting 
              BOMQty = bom.bomQty,
              UnitId = bom.unitId
           join bom 
              where table.itemId == bom.Itemid;
}
Method 2: This method use a single database call.

You can make a view of that query for making computed columns of those fields and replace the null value with an empty value ( for strings ) and 0 (for real values) then drag that view into another query and use the insert_recordset method.

This will insert all the values in the table even the null values in the fields.
//bomqty null check for real and int values
static str bomQty()
{
        DictView dv = new DictView(tableNum(TestView));
        str bomqty = dv.computedColumnString(literalStr(BOM), fieldStr(BOM, BomQty));
        return SysComputedColumn::if(SysComputedColumn::isNullExpression(bomqty),"0",bomqty);
}

// bomunitid null check for string values
static str bomUnitId()
{
        DictView dv = new DictView(tableNum(TestView));
        str bomunitid = dv.computedColumnString(literalStr(BOM), fieldStr(BOM, UnitId));
        return "ISNULL(" + bomunitid + ", '')";
}

// method for inserting data into test table with insert recordset
private void insertDatainTestTable()
{
        Testtable table;
        BOM bom;
        Query query = new Query(queryStr(TestAnotherQuery));
        QueryBuildDataSource qbds;
        QueryBuildFieldList qbdsFldList;
        Map fieldMapping = new Map(Types::String, Types::Container);

        // clearing all fields in query
        query.clearAllFields();

        // getting the Inventtable datasource 
        qbds = query.dataSourceTable(tableNum(TestView));

        // setting the property for selecting the required fields
        qbdsFldList = qbds.fields();
        qbdsFldList.clearFieldList();
        qbdsFldList.dynamic(QueryFieldListDynamic::No);
 
        // adding the fields to list  
        qbdsFldList.addField(fieldNum(TestView,ItemId));
        qbdsFldList.addField(fieldNum(TestView,BOMQty));
        qbdsFldList.addField(fieldNum(TestView,UnitId));  

        // map the query fields to table fields
        fieldMapping.insert(fieldStr(Testtable,ItemId),[qbds.uniqueId(),fieldStr(TestView,ItemId)]);
        fieldMapping.insert(fieldStr(Testtable,ItemId),[qbds.uniqueId(),fieldStr(TestView,BOMQty)]);
        fieldMapping.insert(fieldStr(Testtable,ItemId),[qbds.uniqueId(),fieldStr(TestView,UnitId)]); 

        // add ranges to the query
        query.dataSourceTable(tableNum(TestView)).addRange(fieldNum(TestView, ItemId)).value('A0001');

        // reqiures three arguments described above
        ttsbegin;
        Query::insert_recordset(table,fieldMapping,query);
        ttscommit;
}
Note: By using any of the above method you can insert the null values in tables
For any further information
Ali Ahmed Siddiqui
Ali Ahmed Siddiqui
Software Engineer with hands on experience on Dynamics 365 and power BI