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

Question

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

Rabia Kurnaz 386

Rabia Kurnaz 386 Reputation points

2023-06-15T08:41:58.75+00:00

Like this:
Yitzhak Khabinsky 27,111 Reputation points

2023-06-15T20:48:56.5166667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Answer accepted by question author

2 additional answers

Your answer

Rabia Kurnaz 386 Reputation points

2023-06-15T08:41:58.75+00:00

Like this:
Yitzhak Khabinsky 27,111 Reputation points

2023-06-15T20:48:56.5166667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Answer 1

Viorel 126.9K

Try to split the statement. For example, replace

insert Table1 values
( 1, 2, 3 ),
( 4, 5, 6 ),
( 7, 8, 9 )

with

insert Table1 values
( 1, 2, 3 ),
( 4, 5, 6 )
insert Table1 values
( 7, 8, 9 )

Each fragment should not exceed the maximum allowed number of 1000 row values.

Rabia Kurnaz 386 Reputation points

2023-06-19T13:07:43.39+00:00

Thank you

Answer 2

Olaf Helper 47,616

The number of row value expressions

You provide very lerss informations and so we have to guess:

You have an INSERT statement with a lot of fix VALUE in the manner of

INSERT INTO yourTable (Column1)
VALUES (1), (2), (3), ..

exceeds the maximum allowed number of 1000 row values.

The do what the error imply, reduce the count of values and split the INSERT statement into several ones.

Rabia Kurnaz 386 Reputation points

2023-06-19T13:08:08.52+00:00

i will try thanks your advice

Answer 3

Anonymous

Hi @Rabia Kurnaz

Or you can try the workaround mentioned in this thread.

https://stackoverflow.com/questions/7737472/how-can-i-insert-100000-rows-in-sql-server

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". 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.

0 comments

Share via

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

2 additional answers

Your answer