none
Limit to number of INSERT statements or VALUES clauses

    Question

  • Because of security restrictions, the only way I could load data into a SQL server temporary table was by creating INSERTS in a .sql file and loading that into Management Studio, all 500,000+ of them.

    However, if I ran them in one Execution, the query threw an error. 

    But if I highlighted and executed them about 50,000 at a time, it worked.

    Is there a limit to the number of INSERTS I can perform in a SMSS window?

    I also tried using one INSERT statement and multiple VALUES clauses, but in that case it threw a specific error saying I'm limited to 1000 VALUES clauses. 

    Is the number of VALUES clauses configurable?   Can it be unlimited?


    John Bailo RR Donnelley

    Tuesday, October 29, 2013 9:59 PM

Answers

  • below style has limitation of 1000

    Insert into Table (Column) values ('value1'), ('value2'), ('value3'), ('value4'), ('value5')

    two work around, you can use

    Insert into Table (Column) values ('value1')

    Insert into Table (Column) values ('value2')

    Insert into Table (Column) values ('value3')


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, October 30, 2013 4:28 AM

All replies

  • Please have a look at this link with same question:

    http://stackoverflow.com/questions/7737472/how-can-i-insert-100000-rows-to-mssql


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 29, 2013 10:05 PM
  • From MSDN:

    http://msdn.microsoft.com/en-us/library/dd776382.aspx

    "

    Table value constructors can be used in one of two ways: directly in the VALUES list of an INSERT … VALUES statement, or as a derived table anywhere that derived tables are allowed. The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000. Error 10738 is returned if the number of rows exceeds 1000 in that case. To insert more than 1000 rows, use one of the following methods:

    • Create multiple INSERT statements

    • Use a derived table

    • Bulk import the data by using the bcp utility or the BULK INSERT statement"

    Tuesday, October 29, 2013 10:22 PM
  • Because of the aforementioned permissions issues, one of the commands I cannot access is BULK INSERT.  Nor can I access any external files, or use the import features.

    John Bailo RR Donnelley

    Wednesday, October 30, 2013 1:33 AM
    • INSERTS: My question is about multiple INSERT statements.  There seems to be a limit even to them.  See OP.   Or maybe my query is timing out because there are so many of them?  No specific error is thrown in SSMS.

    • Derived: I tried using a derived table but I cannot use the ODBC driver to access the external .csv file.

    • Bulk: I am prevented from using these utilities by the permissions settings.



    John Bailo RR Donnelley

    Wednesday, October 30, 2013 1:38 AM
  • 1. Try to divide your script into multiple batches using "GO".

    2. Please post exact error for the whole script.

    Wednesday, October 30, 2013 2:30 AM
  • below style has limitation of 1000

    Insert into Table (Column) values ('value1'), ('value2'), ('value3'), ('value4'), ('value5')

    two work around, you can use

    Insert into Table (Column) values ('value1')

    Insert into Table (Column) values ('value2')

    Insert into Table (Column) values ('value3')


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, October 30, 2013 4:28 AM
  • You should be able to use BCP.  Perhaps the real issue here is one of design (followed by a lack of appropriate permissions to do the tasks you've been assigned).  Why are you using a temp table and attempting to load it with so many rows?  500k rows in a temp table should be a concern for any number of reasons.
    Wednesday, October 30, 2013 1:38 PM
  • You should change the form, instead of 

    Insert into Table (Column) values ('value1'), ('value2'), ('value3'), ('value4'), ('value5') 

    use 

    Insert into Table (Column) SELECT * FROM ( values ('value1'), ('value2'), ('value3'), ('value4'), ('value5') ) t (Column)

    it will work with more then 1000

    Thursday, May 29, 2014 4:12 PM
  • To be honest, the real solution to your problem is one of the following two:

    Be granted proper rights to the system you need to update.
    Pass the task to whomever is denying you those rights.

    Its not really fair to expect you to build a house with only a hammer and handsaw.

    Thursday, May 29, 2014 5:16 PM