How to Batch Insert

[liveresume]

Is there any way to batch insert with sqlitex?

[scouten]

If you look at the syntax diagram for the INSERT statement, you'll see that the VALUES clause can repeat, allowing you to insert multiple rows. An example from the interactive sqlite3 console application:

sqlite> CREATE TABLE x(a,b,c);
sqlite> INSERT INTO x VALUES(1,2,3),(4,5,6);
sqlite> SELECT * FROM x;
    a = 1
    b = 2
    c = 3

    a = 4
    b = 5
    c = 6

You should be able to do similar via Sqlitex.

[liveresume]

@scouten I tried binding with a List of Lists, you mean I need to interpolate it myself?

[obmarg]

@liveresume You'd probably need to flatten your list of lists into a single lists if you're trying to bind.

So for example:

records = [[1, 2, 3], [4, 5, 6]]
Sqlitex.query(db, "INSERT INTO values (?, ?, ?), (?, ?, ?)", bind: Enum.concat(records))

However, if you're talking about more than a few rows, constructing a query with a ton of (?, ?, ?)s in it might be more trouble than it's worth. Instead you could just create a prepared statement & a transaction and iterate over your list. Something like:

records = [[1, 2, 3], [4, 5, 6]]
statement = Sqlitex.Statement.prepare!(db, "INSERT INTO values (?, ?, ?)")
Sqlitex.exec(db, "BEGIN TRANSACTION;")
for row <- records do
   Sqlitex.Statement.bind_values!(statement, row)
   Sqlitex.Statement.exec!(statement)
end
Sqlitex.exec(db, "COMMIT TRANSACTION;")

[liveresume]

@obmarg Thanks! Any idea why I'm getting:

statement = Sqlitex.Statement.prepare!(db, "INSERT INTO posts ( user_id ) ")

(Sqlitex.Statement.PrepareError) Prepare statement failed: {:sqlite_error, 'near " ": syntax error'}

Tried a bunch of combos but error message isn't much help.

Error is always on last character, so I tried comma and semicolon. Maybe it's something else?

[liveresume]

Got it, needed 'VALUES'

statement = Sqlitex.Statement.prepare!(db, "INSERT INTO posts VALUES ( user_id ) ")

[obmarg]

Yeah, that looks better. When you get a syntax error, it might be good to refer to the SQL syntax documentation for sqlite. In this case for the insert statement.

I reckon in this case you want INSERT INTO posts ( user_id ) VALUES (?).

[liveresume]

@obmarg There we go. Also I think it should be:

        Sqlitex.exec(db, "BEGIN TRANSACTION;")
        Sqlitex.exec(db, "END TRANSACTION;")

[liveresume]

@obmarg It isn't committing the transaction. Looking into that.

[liveresume]

This seems to work:

        # Sqlitex.exec(db, "END TRANSACTION;")
        Sqlitex.exec(db, "COMMIT;")

Now the only thing left is the id is not autoincrementing:

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

Although it really isn't necessary with sqlite due to implicit row_id.

Also there is no error when this NOT NULL field is left NULL...