Answered

Insert directly into SQL using X++

(1) Share

Report

Posted on by AbdullahAhmed_

170

I'm trying to insert directly into SQL but I get the error: Cannot execute the required database operation.
The SQL database has issued an error.

public static void main(Args _args)
    {

        Connection      connection;
        Statement       statement;
        str             query;
        Resultset       resultSet;

        connection = new Connection();

        statement = connection.createStatement();
        query = 'insert into xdscusttableregular (CustAccount) select ACCOUNTNUM from CUSTTABLE where CUSTTABLE.DATAAREAID = "adc" '
                + 'and CUSTTABLE.INVENTSITEID = "ADC"';
        new SqlStatementExecutePermission(query).assert();
        statement.executeUpdate(query);
        CodeAccessPermission::revertAssert();
        Info('Insertion done');
    }

All responses (3)

Answers (1)

Martin Dráb 229,129 Most Valuable Professional on at

Like (0)

Report

Insert directly into SQL using X++

Did you use insert_recordset statement in X++?
Verified answer

AbdullahAhmed_ 170 on at

Like (0)

Report

Insert directly into SQL using X++

Thank you for your response..

I discovered why the error happening.. it has something to do with quotation mark (" ") and (' ')!

Anyway.. I wanna insert a lot of records in synced table with database (+1.2M records), using x++ to insert these records it takes 2:33 minutes and inserting them in SQL server takes like 2 minutes...

The difference will be more than 30 seconds when inserting +10M records, so I thought to prepare a query string and the insertion happens in the database.
Suggested answer

André Arnaud de Cal... 290,802 Super User 2024 Season 2 on at

Like (0)

Report
Insert directly into SQL using X++

Hi Abdullah,

Is the table xdscusttableregular a table defined in the AOT and synced to the SQL server? Do you get more details than just "The SQL database has issued an error"?

Why do you want to use this statement using direct SQL instead of achieving the same with X++ coding? E.g.

CustTable custTable Xdscusttableregular xdscusttableregular; insert_recordset xdscusttableregular (CustAccount) select AccountNum from custTable where custTable.DataAreaId == 'adc' && custTable.InventSiteId == 'ADC';

This code will be translated in the same statement you wrote and will be fully executed on the SQL server.

Insert directly into SQL using X++

Helpful resources

News and Announcements

Quick Links

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Leaderboard

Featured topics

Product updates

Insert directly into SQL using X++

Helpful resources

News and Announcements

Quick Links

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Subscribe to

Leaderboard

Featured topics

Product updates