SQL insert out of axapta

Hi all. I found a new and interesting question to talk about if non of you mind. As i mentioned in my last topic we made our calculation in out of axapta with stored procedure. We all happy with the performance and the result it made. BUT. Kernel rollup and record versioning turned of my huge smile from my face. Yes maybe you know already i handled the reicd on a really easy way. Since i dont need the recid at all in my tables i set it to -1 and it worked fine. Till now. I need to use the recid field as it is made for. So i found how the recid works in axapta. i can allocate recids for my self without problems. the only question is how to join it to my records. the story is the following. My calculation made by set base sql create some thousand records. It is can not be estimate how many when the procedure starts. So i can made a litte modification at the point i instert records to my tabe to insert it in a temp tablse instead of the real one. So i have the all data i need. I can count how many recid should be allocated. So i allocate them from systemsequences. At this point i have a range. For example from 10 to 120010. it means i have 120.000 record in my tmp table. the next step to insert the tmp table’s records to real table with recids. And this is the question How can i join recid from my range to my records and insert it to table in 1 command. I do not want to use any fetch, trigger or any similar methos.

How about storing all your calculations in the temp table, then return to Axapta and perform a bulk insert (insert_recordset, run on server) into the real table ? Then empty temp table…

Hi, that is not a soulution. Extremly slow that is why this calculation made out of axapta. It is a sql stored procedure what generate a set based SQL an run it.

An insert recordset, called to run on the server is not that slow ?? I can imagine that calculating thousands of records and inserting those one by one is very slow, but a bulk-insert is quite fast. You have all your data available in your temp-data, it will just be huge data-copy inside sql, started from Axapta. Should be acceptable performance !