September 15, 2016 at 10:53 pm
Comments posted to this topic are about the item VALUES limit
September 16, 2016 at 12:53 am
This was removed by the editor as SPAM
September 16, 2016 at 1:12 am
I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?
September 16, 2016 at 1:59 am
Same as 'Select top 1000' default value. Or 'Edit top 200'. Where does those numbers come from?
September 16, 2016 at 2:17 am
paul.knibbs (9/16/2016)
I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?
I suspect it is arbitrary, since you can only insert 1,000 rows if you using the following:
INSERT [Table] (Column)
VALUES (1), (1), (1)....(1);
Yet if you use the derived table syntax:
INSERT [Table] (Column)
SELECT N
FROM (VALUES (1), (1), (1)....(1)) AS t (N);
Then there is no limit, since the limit only appiies when using it directly into the insert list. I tested the latter up to 1,000,000 rows and it still worked fine:
DECLARE @i INT = 100000, @SQL NVARCHAR(MAX) = '';
SET @SQL = CONCAT('DECLARE @T TABLE (N INT); INSERT @T (N) SELECT N FROM (VALUES ',
STUFF((SELECT TOP (@i) ',(1)'
FROM sys.all_objects a, sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
') t (N);');
EXECUTE sp_executesql @SQL;
September 16, 2016 at 2:21 am
September 16, 2016 at 2:28 am
I've been hit by this before, to the extend that I created a Uservoice suggestion for SQLPrompt to give warnings about exceeding this limit... https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/15205332-warning-when-insert-statement-exceeds-limit-of-row
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 16, 2016 at 3:31 am
gareth.davison (9/16/2016)
paul.knibbs (9/16/2016)
I do wonder why the limit is set at that. Is it just an arbitrary number Microsoft plucked from the air, or is there an actual reason for it?I suspect it is arbitrary, since you can only insert 1,000 rows if you using the following:
INSERT [Table] (Column)
VALUES (1), (1), (1)....(1);
Yet if you use the derived table syntax:
INSERT [Table] (Column)
SELECT N
FROM (VALUES (1), (1), (1)....(1)) AS t (N);
Then there is no limit, since the limit only appiies when using it directly into the insert list. I tested the latter up to 1,000,000 rows and it still worked fine:
DECLARE @i INT = 100000, @SQL NVARCHAR(MAX) = '';
SET @SQL = CONCAT('DECLARE @T TABLE (N INT); INSERT @T (N) SELECT N FROM (VALUES ',
STUFF((SELECT TOP (@i) ',(1)'
FROM sys.all_objects a, sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
') t (N);');
EXECUTE sp_executesql @SQL;
+1
Infact, I never realized of this limit, because I always use this syntax: INSERT INTO Table SELECT ....
September 16, 2016 at 3:32 am
From BOL:
The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000
September 16, 2016 at 6:18 am
BrainDonor (9/16/2016)
They are defaults within Tools/Options/SQL Server Object Explorer/Commands (in SSMS 2014 - may be elsewhere in other versions) and therefore configurable.
This affects the defaults within SSMS sessions for the Edit Top <n> or Select Top <n> rows, but not the limit on the VALUES statement. That would have be server specific for all sessions. If configurable, I'd be interested in knowing where or how to change it.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
September 16, 2016 at 6:21 am
Strangely, I knew this one off the top of my head. I haven't hit it since yesterday when building a values clause from data in an Excel file.
September 16, 2016 at 7:09 am
I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2016 at 12:16 pm
Sean Lange (9/16/2016)
I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.
Yeah, I don't like doing it, but it's sometimes easier for the one-time tasks.
September 16, 2016 at 12:38 pm
Ed Wagner (9/16/2016)
Sean Lange (9/16/2016)
I had to look this one up but much as I suspected....the limit is far greater than you would normally encounter. The exception being places where you are building values using dynamic sql or maybe like Ed mentioned using Excel.Yeah, I don't like doing it, but it's sometimes easier for the one-time tasks.
Of course!!! We all have those one-off tasks now and then where you just plug your nose and "git 'er dun". 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 19, 2016 at 2:03 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply