MsSql 1000 row limit on INSERT

Hi

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

Greetings

Eric

Not directly. You can use the "Max INSERT size" setting in the export dialog, but that's byte-length-based, not query count based. I think I should hardcode that 1000 for MSSQL in HeidiSQL. Is that documented for older and newer versions of MSSQL? Perhaps I have to limit that to fewer rows on older servers?

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

Hi Ansgar-

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:

1. A script to create a minimal test database in SQL Server 2012 Express.
2. A 1000 record (exactly) CSV file that should import via HeidiSQL without errors
3. 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

Also, here is a screenshot of the error dialog box that I from within HeidiSQL when I attempt to import more than 1000 records.