web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Can i Insert Data from...
Microsoft Dynamics AX (Archived)

Can i Insert Data from SQL Query in AX Table

(0) ShareShare
ReportReport
Posted on by Maahi P 141

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

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    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.

  • Maahi P Profile Picture
    Maahi P 141 on at

    Thanks @Vilmos Kintera.

    Thanks for your Valuable reply.

    Can we do this in AX 2012 CU9?

    Regards

  • Suggested answer
    Israel Gonzalez Profile Picture
    Israel Gonzalez 746 on at

    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.


  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    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]

  • Maahi P Profile Picture
    Maahi P 141 on at

    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.

  • Suggested answer
    raz Profile Picture
    raz 25 on at

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

  • Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    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.

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans