T-SQL

How to override Table Value Constructor limit of maximum 1000 rows in direct INSERT statement

Regarding MSDN documentation there is a limit that maximum 1000 rows can be constructed using Row Value Constructor when used directly in the INSERT statement. If this limit is reached, error 10738 is generated. There is a simple workaround and we will observe it in detail in this article.

First of all, we will try to check if this limit is really presented regarding the documentation. We will create TestTable and then dynamically create an insert statement with 1005 rows constructed using the VALUES() row constructor. Our final statement generated is visible below in the picture.

CREATE TABLE TestTable ( Value NVARCHAR(100))
GO

DECLARE @Numbers TABLE (ID INT NOT NULL PRIMARY KEY)

INSERT INTO @Numbers
	SELECT TOP(1005) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM [master].[dbo].[spt_values]

DECLARE @Stmt NVARCHAR(MAX) = ''

SELECT @Stmt = @Stmt + '(''' + CAST(ID AS VARCHAR(10)) + '''),'
FROM @Numbers

SET @Stmt = 'INSERT INTO TestTable VALUES ' + LEFT(@Stmt, LEN(@Stmt)-1) + ''

PRINT @Stmt

EXECUTE(@Stmt)
GO

After we have executed our @Stmt, the Messages window in Management Studio shows this output:

VALUES-1000-rows-limit-1

As expected, error 10378 was generated clearly declaring, that we simply can´t ignore the 1000 rows limit for the row constructor.

Luckily there is a simple solution how to skip this limitation: The only one thing we need to do is to move the row  constructor from direct INSERT to derived table as is visible on marked line in this script:

DECLARE @Numbers TABLE (ID INT NOT NULL PRIMARY KEY)

INSERT INTO @Numbers
	SELECT TOP(1005) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM [master].[dbo].[spt_values]

DECLARE @Stmt NVARCHAR(MAX) = ''

SELECT @Stmt = @Stmt + '(''' + CAST(ID AS VARCHAR(10)) + '''),'
FROM @Numbers

SET @Stmt = 'INSERT INTO TestTable SELECT * FROM (VALUES ' + LEFT(@Stmt, LEN(@Stmt)-1) + ') a (Col1)'

PRINT @Stmt

EXECUTE(@Stmt)
GO

Rows in TestTable aren´t inserted directly, but with help of FROM(derived table) trick. There is the final result message:

VALUES-1000-rows-limit-2

Table Row Value constructor is really powerful construct in T-SQL and in the next article we will show other fun we can have with it.

Leave a Reply

Your email address will not be published. Required fields are marked *