Rob Buecker Consulting

Recently ran into a situation where I needed to insert more than 1000 rows into a table using a script.
A little background info: the project is converting data from one SQL Server database into another. The task needs to be scripted but only needs to be written once, meaning, no need for dynamic SQL. Lastly, performance is not a concern.

Msg 10738, Level 15, State 1, Line 1013
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

One solution is to block multiple insert statements with each one containing less than 1000 values, but I found this method to be interesting and doesn’t require the extra work in blocking insert statements:

SELECT col, val FROM (
VALUES	(1, 'a')
	,	(2, 'b')
	,	(3, 'c')
) v ( col, val )
;