When I insert too many data into sqlite database file, an error "too many terms in compound SELECT" occurs. I use "insert into ... select ... union select ... union ...". I know it's too many select statements, but my question is: What's the maximum number of terms in a compound SELECT statement?
Asked
Active
Viewed 1.5k times
13
Smeegol
- 1,844
- 4
- 28
- 41
-
What was the solution in the end? – Muhammad Qasim Nov 12 '19 at 11:18
2 Answers
26
A compound SELECT statement is two or more SELECT statements connected by operators UNION, UNION ALL, EXCEPT, or INTERSECT. We call each individual SELECT statement within a compound SELECT a "term".
The code generator in SQLite processes compound SELECT statements using a recursive algorithm. In order to limit the size of the stack, we therefore limit the number of terms in a compound SELECT. The maximum number of terms is SQLITE_MAX_COMPOUND_SELECT which defaults to 500. We think this is a generous allotment since in practice we almost never see the number of terms in a compound select exceed single digits.
The maximum number of compound SELECT terms can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COMPOUND_SELECT,size) interface.
for more details please check this... http://www.sqlite.org/limits.html
shofee
- 2,018
- 12
- 30
-
8@shobi since older versions of sqlite (<3.7.11) don't allow multiple row insertion using INSERT INTO tablename (columnname) VALUES (value1),(value2), ... it is very easy to end up with a workaround with >500 UNION terms for a simple insertion like INSERT INTO tablename (columnname) SELECT value1 AS `columnname` UNION SELECT value2 UNION SELECT value3 ... UNION SELECT value999 – sdjuan Aug 19 '12 at 23:47
-9
There is no limit for the number of SELECTs you use. All you need to do is check whether the list of columns are matching or not with the INSERT cols.
Teja
- 12,590
- 31
- 87
- 147
-
3in sqlite there is a limit on number on select statements pls check it properly... – shofee Mar 02 '12 at 06:08