INSERT multiple VALUES? about go-sqlite HOT 2 CLOSED

When directly preparing a statement, the resulting Stmt has column-index based setters and Named arg based setters, but neither allow for specifying a row number (and the underlying structures are 1-dimensional).

When using sqlitex Execute or ExecuteTransient functions, the ExecOptions only has 1-dimensional Args and Named fields.

Your understanding is correct and matches what SQLite provides.

I can generate a giant SQL statement directly with no arguments that has multiple rows in VALUES and call ExecuteTransient, but it doesn't benefit from being a prepared statement and I need to do it millions of times.

You can do an INSERT with parameters too:

INSERT INTO foo ( "id" ) VALUES (:row1_id), (:row2_id), (:row3_id);

Am I missing something here?

A couple things, perhaps:

• You can reuse the same prepared statement with Stmt.Reset and Stmt.ClearBindings to run a single-row insert multiple times. This avoids having to re-parse the SQL statement, and is a fairly common way of inserting a lot of data at once.
• If you are performing data modification outside of an explicit transaction (autocommit mode), SQLite will wrap each statement in an implicit transaction. By default, this has the side-effect of performing a full fsync on each commit, which will be very slow. You likely want to start a transaction, perform all your inserts, then commit the transaction.

from go-sqlite.

Much appreciated! The INSERT example you provided would only work with a single column data, as far as I can see there is no way to replace :row1_id with multiple columns worth of data.

I'll do some performance testing, but it looks like using a SAVEPOINT or TRANSACTION around a bunch of INSERTs through a prepared statement will be the way to go.

from go-sqlite.