Microsoft Dynamics AX (Archived)

insert_recordset not working

(0) Share

Report

Posted on by Community Member

Hi All,

When I'm trying to import the bulk data by insert_recordset using below query.

"insert_recordset tradeChartTable (value, PricePlan)

select sum(value), PricePlan from tradeForecastView
group by PricePlan"

Then system throwing below error.

"Cannot execute a data definition language command on KepTradeForecastDataView (KepTradeForecastDataView).The SQL database has issued an error.."

I have checked the eventVwr, there it's given below information.

Incorrect syntax near the keyword 'AS'.. The SQL statement was: "SELECT SUM(T1.VALUE) AS f1,T1.PRICEPLAN AS f2, AS f3,N'kep' AS DATAAREAID,1 AS RECVERSION,IDENTITY(bigint,1,1) AS RECID INTO [##ax_tmp_kep3_87_104040] FROM KEPTRADEFORECASTDATAVIEW T1 WHERE ((DATAAREAID=?) AND (((PRICEPLAN<>?) AND (PRICEPLAN<>?)) AND (EANID=?))) GROUP BY T1.PRICEPLAN ORDER BY T1.PRICEPLAN.

here I don't know why AS f3 came.

Some other findings and information.

My View is a UnionQuery Based view.

My target table is Regular table.

If I change my target table as InMemory table, query is working fine.

Please let me know what I'm missing here, or give me the information why its not working for Regular table.

Nunna

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Iulian Cordobin 8,201 on at

Like (0)

Report

It has to do with the tables you have in your view. How many tables do you have in your view?

Was this reply helpful? Yes No
Iulian Cordobin 8,201 on at

Like (0)

Report

And also wanted to ask, is any of the table(s) you are using in your View an Extended table?

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

Like (0)

Report

Does the select work if you use it outside insert_recordset?

Don't you get any errors when synchronizing database?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

There are two tables in my view. [I mean in union query], and tables are not extended tables, both are just simple independent tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin,

I have no issues while synchronizing table.

All select queries of that table are working very fine, even if I change the same insert_recordSet query to while select, its executing fine.

As I said, the same insert_recordset query is working fine if my target table is In-Memory.

Was this reply helpful? Yes No
DianP 25 on at

Like (0)

Report

Hi Nunna,

I know this is old thread. But somehow I faced the same issue as yours.

I used a view which is from a union query and I want to insert to temporary table with type TempDB for report purpose.

Did you solve your issue?

Was this reply helpful? Yes No