Can i Insert Data from SQL Query in AX Table

(0) Share

Report

Posted on by Maahi P

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

All responses (7)

Answers (1)

Vilmos Kintera 46,149 on at

Like (0)

Report

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 25 on at

Like (0)

Report

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 135 on at

Like (0)

Report

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 46,149 on at

Like (1)

Report

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 708 on at

Like (1)

Report
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 135 on at

Like (0)

Report

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 46,149 on at

Like (0)

Report

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.