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

Rajanikantgohil

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

David_Singleton

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

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

RockWithNAV

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

rxqrxqrxq_zyd

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

bbrown

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

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;