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:
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 thing we need to do is to move the row constructor from direct INSERT to derived table as is visible on the 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:
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.