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 + ", '')"; // 2 single qoutes for empty string in x++

    }

// 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