depends on the number of values sets? depends on the number of bytes in the INSERT statement?
+1
A:
I believe there's no defined number of rows you're limited to inserting per INSERT, but there may be some sort of maximum size for queries in general.
dmags
2010-08-21 03:13:23
+4
A:
You can insert an infinite number of rows with one INSERT statement. For example, you could execute a stored procedure that has a loop executed a thousand times, each time running an INSERT query.
Or your INSERT could trip a trigger which itself performs an INSERT. Which trips another trigger. And so on.
No, it does not depend on the number of value sets. Nor does it depend on the number of bytes.
There is a limit to how deeply nested your parentheses may be, and a limit to how long your total statement is. Both of these are referenced, ironically, on thedailywtf.com . However, both of the means I mentioned above get around these limits.
Borealid
2010-08-21 03:14:04
Your examples are for running multiple INSERT, and it does not show about inserting multiple rows in **one** INSERT statement ..
Lukman
2010-08-21 03:17:09
@Lukman: One INSERT query can result in multiple INSERTS hitting the database. It's just a matter of who's counting what.
Borealid
2010-08-21 03:19:08
true that, but your 1st example show about running query in a loop with each loop running one INSERT statement, and it says nothing about that one INSERT statement inserting multiple rows. it's just a matter of emphasis.
Lukman
2010-08-21 03:24:36
+1
A:
You can insert infinitely large number of records using INSERT ... SELECT pattern, provided you have those records, or part of, in other tables.
But if you are hard-coding the values using INSERT ... VALUES pattern, then there is a limit on how large/long your statement is: max_allowed_packet which limits the length of SQL statements sent by the client to the database server, and it affects any types of queries and not only for INSERT statement.
Lukman
2010-08-21 03:22:02