On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote:
> What is the best ?
>
> INSERT INTO artists (name) VALUES
> ("Gene Vincent") ...
> ("Moi _ Me");
>
You're missing commas.
And you should not use double-quotes but single-quotes for string-literals.
> I want to insert 1 000 000 records.
> The other manner tot do is creating a transaction with one insert command
> by line.
>
In all cases, you should have as few transactions as possible IMHO,
typically a single one.
Whether to have one statement per-row or one-statement for multiple-rows is
separate from transactions.
The multi-row insert statement might hit the parser limit if too large.
I've converted in the past a very large
insert-per-row SQL file, to one-insert-per-table (for all that table's row)
and ran into that limit. So I gave up,
since hard to know how many rows to put per statement.
And as Gunter wrote, SQLite will need to parse each statement in full in
memory, so the larger the statement
the more memory used.
If you're doing massive inserts from text files, maybe from a CSV rather
than SQL file might be faster.
You're trading one parser (SQL) for another (CSV), but since DRH wrote
both, and CSV is significantly
simpler than SQL (for parsing), it's possible CSV might have an edge. But
that remains to be seen.
> My question is what is the best thing to do ?
> Or having a transaction for the first sample?
>
> One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION" because
> others threads needs to access to tables.
>
Unless you're using WAL, other threads will be blocked during inserts,
whether you use a
single transactions, or not. They might be able to "sneak-in" betweeb
inserts TX if using multiple TXs,
but then you might block the "inserter".
> Please help me.
>
Programmatically, prepare() and bind() as Gunter wrote again. --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
