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 / Finance | Project Operations, Human Resources, ... / Help with insert_Recor...
Finance | Project Operations, Human Resources, ...
Unanswered

Help with insert_RecordSet

(0) ShareShare
ReportReport
Posted on by Kaushik Parul 47

Hi Team,

I have a while statement which I want to convert in insert_RecordSet.

Table1      table1;
Table2      table2;
SalesTable  salesTable;

 while select table1
    join salesTable
        where table1.SalesId == salesTable.SalesId
            && salesTable.SalesStatus != SalesStatus::Backorder
    {
        table2.SalesId        = table1.SalesId;
        table2.CustAccount    = table1.CustAccount;
        table2.CreatedDate    = table1.CreatedDate;
        table2.SalesId        = table1.SalesId;
        table2.insert();
        
        delete_from table1
            where table1.SalesId    == table2.SalesId;
    }

I tried with below code but its not working..please help if its possible to convert this statement to insert_RecordSet?

Thanks in advance!!!!

 insert_recordset table2 
        (SalesId, CreatedDate, CustAccount)
    
    select SalesId, CreatedDate, CustAccount from table1
    join salesTable
        where table1.SalesId== salesTable.SalesId
            && !salesTable.SalesStatus   == SalesStatus::Backorder;

  • Kaushik Parul Profile Picture
    Kaushik Parul 47 on at
    RE: Help with insert_RecordSet

    You're saying that this query:

    is returning all the data from table1 and not checking the "not open" condition. Is that a correct description of your problem?

    Yes that's right...

  • Martin Dráb Profile Picture
    Martin Dráb 231,801 Most Valuable Professional on at
    RE: Help with insert_RecordSet

    Let's focus on a single problem at once. You're talking about things like deleting data from Table1, but that's not what the insert_recordset statement does, therefore let's ignore it for now.

    Also, you seem to be saying that you have a problem already with the query, therefore we maybe can forget insert_recordset as well.

    You're saying that this query:

    select SalesId, CreatedDate, CustAccount from table1
    join salesTable
        where salesTable.SalesId == table1.SalesId
           && salesTable.SalesStatus != SalesStatus::Backorder;

    is returning all the data from table1 and not checking the "not open" condition. Is that a correct description of your problem?

    I must admit that I don't see anything wrong with the query. Just note that you could use exists/not exists joins instead of the inner join.

  • Kaushik Parul Profile Picture
    Kaushik Parul 47 on at
    RE: Help with insert_RecordSet

    Hi Martin,

    Thanks for the this..the error is gone now but the condition for open sales order is not working in this case.. Here what I am trying to do is...

    Table1 has open sales order data..I am matching the status of the table1 records with salestable that whether the reocrd is still in open status in salestable...if its not open status the I am inserting the data in table2 and deleting it from table1.

    (i.e., table1 should have only open sales orders data..if the status of the table1 sales orders has changed from open status to something else then I am deleting that record from table1 and inserting it to table2)

    when running this insert record set condition..its just filling in all the data from table1 to table2 and deleting all records from table1 but not checking the "not open" condition.

    Will this insertrecord set not work in this condition??

    or is this condition incorrect?

    please help..

  • Martin Dráb Profile Picture
    Martin Dráb 231,801 Most Valuable Professional on at
    RE: Help with insert_RecordSet

    I'm not sure if it's related, but please change

    && !salesTable.SalesStatus == SalesStatus::Backorder;

    to

    && salesTable.SalesStatus != SalesStatus::Backorder;

  • Kaushik Parul Profile Picture
    Kaushik Parul 47 on at
    RE: Help with insert_RecordSet

    HI Martin,

    I get the error :

    Cannot execute a data definition language command on Table1.

    The SQL database has issued an error.

    SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='.

  • Martin Dráb Profile Picture
    Martin Dráb 231,801 Most Valuable Professional on at
    RE: Help with insert_RecordSet

    Could you tell us more about the problem, please? "Its not working" isn't very specific.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,933 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,801 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans