Categorized | Featured

avatar

Dynamics AX Record Inserts Using T-SQL

Have you ever tried to insert records into tables in Dynamics AX 2009 or Dynamics AX 2012 using T-SQL, wondering whether you got all the fields, and stumbled over RecId? Have you inserted records just to find that data consistency was hosed when you were done?

It can be extremely useful to know how to do it, especially if you are doing some level of ETL. For example, we recently inserted 30 million U.S. ZIP+4 codes into Dynamics AX—the same number of postal codes that exist in the United Kingdom—and I think we all know how dreadfully slow doing that through X++ can be.

RecId numbers are assigned from a number sequence that Dynamics AX stores in a system table with a reference to the Dynamics AX table number, or TableId. We need a different approach. First, the table storing all table names with their IDs is a table called SQLDICTIONARY, so our first task is to find our TableId from that. Let’s assign it to a scalar variable, shall we?

Simple, right? Now go load your own data.

For more information on how to insert a record in a Dynamics AX table, contact our Dynamics AX support center at axaptasupport@bkd.com.

This post was written by:

Jesper, a senior managing consultant with BKD Technologies, has more than 25 years of experience as a technology consultant, performing software development and implementation of Microsoft solutions. He has more than 16 years of experience working with Microsoft Dynamics AX software and helps generate new opportunities through the Midwest.


Leave a Reply

Stay Connected

Follow us on Twitter
Follow Us on Twitter

Want updates about regulatory, tax, compliance and other issues?

Watch us on YouTube
Watch Us On YouTube

Watch us on YouTube, where you can see what makes BKD stand out from the rest.

BKD RSS feeds
Subscribe Via RSS

Subscribe via RSS to stay up to date on the latest BKD news and information.

 

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Recent Comments

Buffer