SQLite Issue when inserting large number of record

问题:

I am trying to insert 15530 record in a certain table using SQLite3 shell but i get that error , I searched for the solution SQLITE_MAX_COMPOUND_SELECT which defaults to 500 is the reason , but i don't know how to change it using Shell.

"Error: too many terms in compound SELECT"

http://desmond.imageshack.us/Himg861/scaled.php?server=861&filename=sqlite.jpg&res=landing

INSERT INTO table_name (my_id, my_name) VALUES
(1, 'Aaliyah'),
(2, 'Alvar Aalto'),
(3, 'Willie Aames'),
...
(15530, 'name');

回答1:

INSERT INTO doesn't work that way.

Try this:

BEGIN TRANSACTION
INSERT INTO author (author_id, author_name) VALUES (1, 'Aaliyah')
INSERT INTO author (author_id, author_name) VALUES (2, 'Alvar Aalto')
INSERT INTO author (author_id, author_name) VALUES (3, 'Willie Aames')
...
END TRANSACTION

http://www.sqlite.org/lang_insert.html

回答2:

The multiple-value INSERT INTO syntax was introduced in SQLite 3.7.11, so the original syntax is fine on recent versions of SQLite. On older versions, you can use an alternative syntax.

However, the limit SQLITE_MAX_COMPOUND_SELECT cannot be raised at runtime, so you need to split your inserts into batches of 500 rows each. This will be more efficient than inserting one row per query. E.g.

BEGIN;
INSERT INTO table_name (id, name) VALUES (1, 'foo'), ..., (500, 'bar');
INSERT INTO table_name (id, name) VALUES (501, 'baz'), ..., (1000, 'zzz');
...
COMMIT;