Sqlx: bulk insert

Created on 17 Apr 2015 · 9Comments · Source: jmoiron/sqlx

I would appreciate any hints how to perform a bulk/batch insert.
I ve been looking at the test suit and I could not figure out how.

Source

areski

Most helpful comment

This would be a good improvement. I also need something like that. I would appreciate one day have this feature at hand.

hrugani on 18 May 2018

👍18

All 9 comments

Do you want to do a parameterized bulk insert? Eg:

INSERT INTO foo (a,b,c) VALUES (?, ?, ?), (?, ?, ?), ...;

If this is the case, sqlx does not provide any helpers for this, although if you are using a db that uses $1, $2, ... bindvars instead of the ? bindvar, you can build it with ? (which will be easier) and then rebind it.

jmoiron on 17 Apr 2015

I'm actually using NamedExec, my insert look like this:

sqlQuery := "INSERT INTO mytable (field1, field2) VALUES (:field1, :field2)"
res, err = tx.NamedExec(sqlQuery, vmap)

Is there a way to exec a Dry run wtih NamedExec, in order to copy the SQL in a buffer and then Exec all the insert in one call?

areski on 17 Apr 2015

NamedExec and BindNamed have to take the names and convert them into a []interface{} for execution with Query. There's no way currently to do this in bulk, you have to call it each time. This, for instance, is not handled:

type thing struct { f1, f2 string }
var things []thing{...}
Query(`INSERT INTO mytable (field1, field2) VALUES (:f1, :f2), (:f2, :f2)`, things)

jmoiron on 22 Apr 2015

Okey that's a pity. I will close the issue, feel free to reopen if needed.

areski on 23 Apr 2015

I would love to see this work, especially since something very similar can be done with DB.Exec from the stdlib sql/database package. The different being, of course, that this would allow for naming the fields that are inserted.