Skip to main content

Notifications

Microsoft Dynamics AX forum

Can i Insert Data from SQL Query in AX Table

Posted on by 135

Hi there,

             I have created a Test table i.e;testtable from AOT, i want to run some query in SQL to bring some data from different tables of AX Database and stores in my testtable, but i am facing the Problem with RECID Field.

          Is there any way to add some dummy values in RECID Column.

Thanks.

*This post is locked for comments

  • Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Can i Insert Data from SQL Query in AX Table

    I have checked the above linked blog and it has 0 mentions of SQL, the post is unrelated to the problem discussed in this topic.

  • Suggested answer
    raz Profile Picture
    raz 25 on at
    RE: Can i Insert Data from SQL Query in AX Table

    There is an answer there kashperuk.blogspot.com.mt/.../development-tutorial-insertrecordset.html

  • Maahi P Profile Picture
    Maahi P 135 on at
    RE: Can i Insert Data from SQL Query in AX Table

    Thanks @Vilmos Kintera

    Thanks for your Valuable information and also for your article which guided us the importance of RECID in AX.

    Really appreciated your work for guiding me in correct direction.

    Thanks.

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Can i Insert Data from SQL Query in AX Table

    The above suggestion for fetching RecId like that is absolutely incorrect, since it completely ignores not committed transactions that have already fetched that RecId, and also AOS cached RecId reservation. I have written an article about why inserting directly in SQL is bad, please make sure you read it:

    [View:https://www.daxrunbase.com/2017/06/13/update-ax-database-tables-from-sql/:750:50]

  • Suggested answer
    Israel Gonzalez Profile Picture
    Israel Gonzalez 708 on at
    RE: Can i Insert Data from SQL Query in AX Table

    Hi Maahi, you can do it but you have to specify the DataAreaId, Partition, RecVersion and RecId, i.e.:

    INSERT INTO YourAxTable
    (
       DATAAREAID
      ,PARTITION 
      ,RECVERSION
      ,RECID
    )
    SELECT 'dai'
           ,5637144576
           ,1
           ,ISNULL((SELECT MAX(RecId) FROM YourAxTable),0) +
    		ROW_NUMBER() OVER(ORDER BY ImportantSortField)
    FROM YourOtherTable


    Regards, please don't forget mark as verify answer.


  • Maahi P Profile Picture
    Maahi P 135 on at
    RE: Can i Insert Data from SQL Query in AX Table

    Thanks @Vilmos Kintera.

    Thanks for your Valuable reply.

    Can we do this in AX 2012 CU9?

    Regards

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Can i Insert Data from SQL Query in AX Table

    Short answer, no. In Production Dynamics 365 for Finance and Operations deployments the infrastructure including the database is not accessible. You have to use Data Entities in case you want to pull information from external sources. In the current version direct database access is not supported like it used to be in AX 2012, but you could do file-based imports. Look up what Data Import eXport Framework (DIXF) is, that is what you could use to pull in information from external sources.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,807 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans