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

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

Best Answer

  • David_SingletonDavid_Singleton Member Posts: 5,475
    Answer ✓
    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;
    

    David Singleton

Answers

  • JuhlJuhl Member Posts: 728
    Dont ever Insert from sql. You wont hit trigger code.
    Follow me on my blog juhl.blog
  • RockWithNAVRockWithNAV Member Posts: 1,138
    Dont do until and unless its your custom table and you know all the possible outcomes.
  • rxqrxqrxq_zydrxqrxqrxq_zyd Member Posts: 16
    actually, it is very dangerous, since you know all the triggers for fields
  • bbrownbbrown MAMember Posts: 3,213
    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.
    There are no bugs - only undocumented features.
  • David_SingletonDavid_Singleton Member Posts: 5,475
    Answer ✓
    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;
    

    David Singleton
Sign In or Register to comment.