SQL Server Maximum rows that can be inserted in a single insert statment

by

The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES... i.e.

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3),...... upto 1000 rows.

But if your are using a SELECT statement to insert rows in a table, there is no limit for that, something like…

INSERT INTO TableName (ColName)
Select Col FROM AnotherTable

Now coming to your second question. What happens when an error occurs during an insert.

Well if you are inserting rows using multi-value construct 

INSERT INTO TableName( Colum1)
VALUES (1),
       (2),
       (3)

In the above scenario if any row insert causes an error the whole statement will be rolled back and none of the rows will be inserted.

But if you were inserting rows with a separate statement for each row i.e. …

INSERT INTO TableName( Colum1) VALUES (1)
INSERT INTO TableName( Colum1) VALUES (2)
INSERT INTO TableName( Colum1) VALUES (3)

In the above case each row insert is a separate statement and if any row insert caused an error only that specific insert statement will be rolled back the rest will be successfully inserted.

More Related Contents:

  1. How do I use cascade delete with SQL Server?
  2. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  3. How to add Active Directory user group as login in SQL Server
  4. Maximum size of a varchar(max) variable
  5. How to update large table with millions of rows in SQL Server?
  6. How to Use UTF-8 Collation in SQL Server database?
  7. How do you specify a different port number in SQL Management Studio?
  8. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  9. Export table to file with column headers (column names) using the bcp utility and SQL Server 2008
  10. Sequence as default value for a column
  11. Aggregate bitwise-OR in a subquery
  12. How to connect to MS SQL Server using Inno Setup?
  13. SQL count rows in a table
  14. Convert month name to month number in SQL Server
  15. Decimal values in SQL for dividing results
  16. How do I programmatically set the connection string for Entity-Framework Code-First?
  17. Need help in calculation using two Datasets using Expression SSRS
  18. SQL Server Xml query with multiple namespaces
  19. T-SQL incrementing counter based on change in a column value
  20. How can I fill a column with random numbers in SQL? I get the same value in every row
  21. The request failed or the service did not respond in a timely fashion?
  22. Bulk insert fixed width fields
  23. “No backupset selected to be restored” SQL Server 2012
  24. Calculate Time Difference Between Two Rows
  25. comma delimited list as a single string, T-SQL [duplicate]
  26. What is the difference between Cartesian product and cross join?
  27. sql geography to dbgeography?
  28. How to Bulk Insert from XLSX file extension?
  29. Count number of records returned by group by
  30. SQL Server 2008 – order by strings with number numerically

Leave a Comment