A Quick Solution for ‘INSERT statement exceeds the maximum allowed number of 1000 row values’

If you’re building a large INSERT statement using VALUES, you might find yourself hitting the limit of 1000 rows, for example:

INSERT INTO #Numbers (Number, InWords)
VALUES
    (1, 'One'),
    (2, 'Two'),
    (3, 'Three'),
    ...  /* records 4-999 */
    (1000, ' One Thousand '),
    (1001, ' One Thousand and One');

INSERT statement exceeds the maximum allowed number of 1000 row values

Folks may tell you there are better ways to solve the issue, but the likelihood is that you’re doing it this way for a particular reason. So let’s skip the spiel and jump straight to a simple solution.

You want to wrap your VALUES into a derived table, so it’ll look like this:

INSERT INTO #Numbers (Number, InWords)
SELECT Val, Txt
FROM (VALUES
	(1, 'One'),
    (2, 'Two'),
    (3, 'Three'),
    ... /* records 4-999 */
    (1000, ' One Thousand '),
    (1001, ' One Thousand and One')
	) v (Val, Txt);

SQL Server limits INSERT ... VALUES statements to 1000 rows. Wrapping your existing list of values in a derived table and inserting the contents of that will bypass it. INSERT ... SELECT has no such limit.

The documentation warns of this limitation and also tucks away the solution.

I’ll warn here that doing this with excessive numbers of records does degrade performance and make SSMS a little unresponsive. That’s likely due to syntax highlighting and formatting for a large single statement. Interestingly, Visual Studio doesn’t suffer the same fate. Ask me how I know, heh 😅

So that’s all there is to it. No lectures, one tweak, job done.