14

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?

Smeegol
  • 2,014
  • 4
  • 29
  • 44

1 Answers1

28

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,079
  • 13
  • 30
  • 9
    @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