I want to INSERT item from SQL, Anybody have statndard SQL Query or list of table name ?

Rajanikantgohil · 2021-08-10

I want INSERT query for sql to NAV or list table tame for ITEM.

David_Singleton · 2021-09-01

Create a new table in NAV That has no validation, and simply contains the core data you need to create an Item. Populate this table direct from SQL.

Then in NAV have a job that runs through this new table and inserts items, something like

MySQLItem.Reset
MySQLItem.Setrange(Created,FALSE)
IF MySQLItem.FINDSET THEN
  REPEAT
    Item.INIT;
    Item.VALIDATE("no.",MySQLItem.ItemNo);
    IF Item.INSERT(TRUE) THEN BEGIN
      Item.VALIDATE("Some Field",MySQLItem.MoreFields...);
      ......
      Item.MODIFY(TRUE);   
    END;
   MySQLItem..Created := TRUE;
   MySQLItem.MODIFY;
  UNTIL MySQLItem.NEXT = 0;

Juhl · 2021-08-10

Dont ever Insert from sql. You wont hit trigger code.

RockWithNAV · 2021-08-11

Dont do until and unless its your custom table and you know all the possible outcomes.

rxqrxqrxq_zyd · 2021-08-16

actually, it is very dangerous, since you know all the triggers for fields

bbrown · 2021-08-16

Another problem is related to the behavior of the NAV service tier cache. Since "Item" is a commonly used table I'd say it's safe to assume many of its records would often be cached on the service tiers. Any changes made via direct SQL would not become apparent to other users until something else triggered an update of the cache. This is a common problem with direct to SQL updates with the 3 tier environments.

David_Singleton · 2021-09-01

Create a new table in NAV That has no validation, and simply contains the core data you need to create an Item. Populate this table direct from SQL.

Then in NAV have a job that runs through this new table and inserts items, something like

MySQLItem.Reset
MySQLItem.Setrange(Created,FALSE)
IF MySQLItem.FINDSET THEN
  REPEAT
    Item.INIT;
    Item.VALIDATE("no.",MySQLItem.ItemNo);
    IF Item.INSERT(TRUE) THEN BEGIN
      Item.VALIDATE("Some Field",MySQLItem.MoreFields...);
      ......
      Item.MODIFY(TRUE);   
    END;
   MySQLItem..Created := TRUE;
   MySQLItem.MODIFY;
  UNTIL MySQLItem.NEXT = 0;

Howdy, Stranger!

Quick Links

Categories

I want to INSERT item from SQL, Anybody have statndard SQL Query or list of table name ?

Best Answer

Answers