Finance | Project Operations, Human Resources, ...

Use InsertRecordset without specifying all fields

(0) Share

Report

Posted on by junior AX

1,552

Hi,

Let's say PriceDiscTable has 10 records when i filter by a certain field

I want to create a new customer and if this customer has the same certain field then i need to insert those 10 records in priceDiscTable for that new customer.. Same data just the account relation is different

How can i do it using recordInsert without specifying all the fields in the select statement it should take them automatically

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

Hi junior AX,

I'm not sure if I understand the question.

Are you asking how to insert 10 records with one database operation - and provide some fixed value for some field for all those records?

Or, are you asking how to do this with insert_recordset?

Regarding insert_recordset, it always copies data from one table to another, as you can find in the docs page: https://docs.microsoft.com/en-us/dynamicsax-2012/developer/insert-recordset . So if your question is about how to achieve your requirement with insert_recordset, I think it's not possible.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

Ah, or do you mean that you would like to use insert_recordset to copy all fields, and not having to specify each field separately? That's also not possible, as you will see in the documentation.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Nikolaos,

I want to insert the same data for those 10 records except that the accountRelation will be different

What is the best way to do it.. And why insert record won't work

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report
insert_recordset works, but you have to mention each column that you want to copy.

If you don't want to do that, you can do something like this:

MyTable myTable1, myTable2; while select myTable1 { myTable2.clear(); myTable2.data(myTable1); myTable2.SomeField = 'foo'; myTable2.insert(); }

And if you want to insert all records in one database call, you can use recordInsertList.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Nikolas,

Which one is better for perfomance, insertRecordSet or insert recordList or the while loop

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

Hi juniorAX,

insertRecordSet and RecordInsertList can be used to insert many records in one database call.

If you make many inserts in a while loop, you'll do one database call for each insert.

It's faster to make one database call than many calls. But it doesn't really matter in your scenario since you're just inserting 10 records when you create a new customer.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Nokolas,

It might be more than 10 records. 10 records was an example.

So it might be 300 records

Does insert_recordset use one database call?

Also i noticed that recordInsertList uses a while loop

So now which one is the best approach

Here's my logic.. Not sure if it's correct

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

Hi, you can use either one, if both suit your requirements. Your code is missing some details from my example, but you can choose whether to follow the example or not. Regarding your other questions, you can look in the product documentation.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

The while loop condistion in the picture above is wrong. It's priceDiscTableLocal. AccountRelation ==priceDiscTable. AccountRelation

Was this reply helpful? Yes No
Martin Dráb 237,996 Most Valuable Professional on at

Like (1)

Report

Your original requirement can be solved with Query::insert_recordset().

Both insert_recordset and Query::insert_recordset() uses a single database call, unless it's forced to use row-by-row processing. See Maintain Fast SQL Operations, for example.

Was this reply helpful? Yes No