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

Notifications

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) / SQL insert into RouteO...
Microsoft Dynamics AX (Archived)

SQL insert into RouteOprTable

(0) ShareShare
ReportReport
Posted on by lukbel 282

Hi all

Running AX 2012 R3 CU9

Looking at operations and we have a large number of records to add to RouteOprTable. It seems like a relatively straightforward table, and had no direct dependencies I could see, so the plan was to use SQL to load the data.

I used the following query on the SQL query server as a test:

INSERT INTO <database>.dbo.ROUTEOPRTABLE (OprId, Name, RecId,dataareaid) VALUES ('2','In = 2A',2,<our dataareaID>);

This created a record in the SQL database as expected, OprID value "2", the name as "In = 2A", a record-ID of 2, and the correct dataareaID.

However we can't see it when we look at the actual RouteOprTable in the AOT table browser.

We have used this method before and it has worked, is there something about this table/the method I am missing?

Cheers

Luke

*This post is locked for comments

I have the same question (0)
  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at

    Hi,

    you should avoid such direct access ax database DML commands in all cases. Exception: You are really knowing what you are doing and what are the consequences.

    Think of: RecID generation, maybe other PartitionID and moreover the built in caching mechanism of Ax kernel.

    Maybe its better to 'import' your data in some kind of worktable in Ax and use Ax commands like insert_recordset for set based bulk inserts.

    What you are trying to do is neither supported nor recommended in any case.

    regards Douglas

  • lukbel Profile Picture
    lukbel 282 on at

    Hi Douglas,

    Thanks for your reply. I am aware of the potential risks of direct SQL, and am doing this in our Test system to check if there are any bugs before we actually commit to it.

    Could you possibly provide more information re: "AX commands like insert_recordset", or provide some information w/r/t what the problem with the current SQL load may be?

    Thanks very much for your help in this matter

    Luke

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    You can find more information about insert_recordset by simply putting this keyword to a search engine; you don't have to waste time by waiting for a reply here.

    Anyway, here is the documentation on MSDN, for instance: insert_recordset [AX 2012].

  • lukbel Profile Picture
    lukbel 282 on at

    Hi Martin

    I had already done that and found this page - my problem is I'm unsure exactly what it is I'm looking at.

    Is this a built-in class, a job, X++ syntax, some kind of external framework?

    Apologies for not wording my question in a more specific way

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    It's an X++ operator ("insert_recordset is a record set-based operator" to quote the documentation), i.e. "X++ syntax" in your words. If you look at the examples in documentation, you'll clearly see that it's not syntax for creating a class, calling a job or anything like that.

  • lukbel Profile Picture
    lukbel 282 on at

    I think I see - I should upload it to a secondary table in AX, then create a job that calls (for example)

    insert_recordset RouteOprTable (OprId, name)

       select [fields from other table]

           from [othertable]; (and then add any necessary parameters to that)

    Or is there a way of creating some kind of temporary table by passing it a CSV?

    I feel like there's some part of my understanding that's missing here, that sounds like double-handling.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    You can use the Data Import Export Framework (DIXF) to import data to AX from CSV (and other formats).

    I would simply use that without writing any SQL or X++.

  • lukbel Profile Picture
    lukbel 282 on at

    If it's all the same, I'd rather avoid the DIXF.

    It's only 3000 or so records in a one-off upload, and in the amount of time it takes to get a working template and test it, I'd have been as well doing it by hand.

    At this point I may just do that.

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    That's not my experience. If it's just a simple table, you create a new entity of type "table", create a sample file, fill it with data and import it. It's work for a few minutes - you don't have to design, write and test anty code, you can't make any bugs in code, you can't screw up RecId sequence, you don't have to deploy anything...

  • Lee Bailey Profile Picture
    Lee Bailey 340 on at

    There is an out of the box entity for RouteOprTable.

    Like Martin says the amount of work to get these imported safely is minutes.

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

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans