MsSql 1000 row limit on INSERT
MsSQL has a 1000 row limit on INSERT statement
Is there any setting in HeidiSQL where a larg INSERT can be broken down to multiple INSERTs of 1000 rows
Hi Ansgar -
I am having the same problem on SQL Server 2012. Shame that the original poster didn't get back with you about links to info on this topic.
I am not especially proficient on SQL (any server), but I am trying to import CSV files with 10K to 20K records in HeidiSQL when I ran into this issue.
I researched the problem and found the following technical explanation for the issue and why it won't probably be relaxed, which apparently is an issue on Oracle as well as SQL Server. Especially read the last poster's proposed workaround using a single insert with multiple UNIONs of 1000 rows max each - it appears easy to implement, but I have no idea if it is feasible or if it works well. Here is the link: http://dba.stackexchange.com/questions/82921/the-number-of-row-value-expressions-in-the-insert-statement-exceeds-the-maximum
Here is a another technical article (mostly syntax and other such issues) as well. It acknowledges the 1000 row limit, but doesn't say why it is there: https://technet.microsoft.com/en-us/library/ms187905(v=sql.105).aspx
Thanks for a great product! It would be wonderful to get a solution to this soon as I have many more very large imports to make :-)
Best regards, Don
If you are able to fix this, I will be happy to test it for you on SQL Server 2012 Express.
I have attached 3 files that contain:
- A script to create a minimal test database in SQL Server 2012 Express.
- A 1000 record (exactly) CSV file that should import via HeidiSQL without errors
- A 5000+ record CSV file to generate the error dialog box in HeidiSQL that I get and to test any possible fixes in HeidiSQL.
When importing these files into the database that I provided a script to create, you must unselect the field named "InsertID" in the HeidiSQL dialog box as this is the identity field for the database and the CSV file does not include this column.
Again, thanks so much for a great open source product!
Best regards, Don
Please login to leave a reply, or register at first.