Insert directly into SQL using X++

(2) Share

Report

Posted on by AbdullahAhmed_

525

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)

Sort by

Suggested answer

André Arnaud de Cal... 307,097 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!
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.
Verified answer

AbdullahAhmed_ 525 on at

Like (0)

Report
Copy link

Link copied!

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.
Martin Dráb 240,742 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Did you use insert_recordset statement in X++?