web
You’re offline. This is a read only version of the page.
close
Skip to main content
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

I have the same question (0)
  • 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.

  • Maahi P Profile Picture
    Maahi P 141 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
    Israel Gonzalez Profile Picture
    Israel Gonzalez 733 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.


  • 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]

  • Maahi P Profile Picture
    Maahi P 141 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.

  • 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

  • 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.

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans