How to efficiently insert 10,000 rows in SQL Server-CodePudding

I'm trying to load roughly 10,000 entries into a SQL Server table, and have found it doesn't allow loading over 1,000 row values. The error is shown below:

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

Currently I'm trying to run a simple SQL script inside Data Studio.

INSERT INTO [<table>] (<COLUMNS>)
VALUES (<rows>)

I was wondering if there was any way to bypass this error within SQL Server, or if I need a separate script which then loads the data onto the server.

CodePudding user response：

You could use MERGE (T-SQL) You can create a user defined table type and use sql-merge to insert, update and delete large volume of data at once.

CodePudding user response：

I was also facing the same issue in the past, I used go command after each insert block, it worked.

example:

insert into table_name(col1)
values(1)
go
insert into table_name(col1)
values(2)
go
insert into table_name(col1)
values(3)
go
.
.
.
insert into table_name(col1)
values(n)
go