sqlwrite: INSERT statement exceeds the maximum allowed number of 1000 row values..

Hi,

I have been using sqlwrite with no problem until upgrading to 2022b. I got below error when using sqlwrite function

number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values..

Did Matlab change the behavior of sqlwrite? How can i resolve this issue?

[Rik]

Are you indeed inserting more than 1000 rows in one statement?

[Polsak Tothong]

Yes I did. However, in the preqious version, there was no issue with this.

[Rik]

I can't find anything in the release notes, but the documentation does warn that "The Microsoft® Access™ ODBC driver demonstrates unexpected behavior during large inserts. When you insert a large amount of data with Microsoft Access, insert the data in batches. For example, if you want to insert 100,000 rows of data, insert 10,000 rows at a time."

I hardly ever work with databases, so I'm not entirely sure I can help you, but which version did you use previously?

[Harsh Sanghai]

The error looks like it comes from SQL (see here https://dba.stackexchange.com/questions/82921/the-number-of-row-value-expressions-in-the-insert-statement-exceeds-the-maximum) .

Answer 1

[Harsh Sanghai]

As per my understanding it is a limitation of SQL Server, which they have in place to reduce compilation times.

Refer to the following link that explains the limitation and ways you can insert over 1000 rows while avoiding the message:

https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16