How to fix The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Question

I am new to SQL and was trying to copy a database for a project.

However, the commands didn't execute accounting to the error

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

How do I resolve this.

https://docs.google.com/document/d/1-WhNRX1iYJIz7e5l28DMPWgsPklpE_w6/edit

The above link is the source file what I am creating a database from.

Answer 1

One option is that you can split your insert statement from one big INSERT into statement to multiple INSER into with items <=1000 rows for each INSERT in your script.

Answer 2

As the error messages says, in SQL Server you cannot have more than 1000 rows in an INSERT VALUES clause. This may seem like a corny restriction, but it is there to protect you from sever performance issues. If you instead use SELECT UNION ALL SELECT etc, there is no limitation, you can insert 50000 rows that way, but the performance is horrible. Not to execute the statement, but to parse and compile it.

Anyway, in your case, I guess you need to use the scripting language of your choice to break up the statements so they have fewer rows. The simplest is probably to only have one VALUES per INSERT, but that will be slow for other reasons. Then again, you can improve speed quite a bit in that case by wrapping the operation in BEGIN TRANSACTION and COMMIT TRANSACTION.

Answer 3

You can also use

Insert into table

select * from

(values (…….) v( coll,…)

it does not have that limitation.

Answer 4

Hi @PRANAY KUMAR PANDEY

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

When used as a derived table, there is no limit to the number of rows.

When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1000 rows. Error 10738 is returned if the number of rows exceeds the maximum. To insert more than 1000 rows, use one of the following methods:

• Create multiple INSERT statements with VALUES clauses <= 1,000 lines.
• Use a derived table
• Bulk import the data by using the bcp utility, the .NET SqlBulkCopy class, OPENROWSET (BULK ...), or the BULK INSERT statement.

Please refer to this doc for more details: Table Value Constructor (Transact-SQL)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Just ran into this and tried a work-around.

Turns out using the

INSERT INTO Foo SELECT bar FROM (Values (1), (2), (3)) x(bar)

syntax is not subjected to that 1000 limit