bulk insert

[areski]

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.

[jmoiron]

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.

[areski]

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?

[jmoiron]

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)

[areski]

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

[dcormier]

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.

[hrugani]

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

[ijhuang]

Would also like to see a helper for bulk inserts!

[cgebe]

duplicate #238
already fixed in hmgle@018bd2d

Could you pull these changes?

[dcormier]

There's even an open PR to resolve this. #285.

[wubin1989]

There is a bug when insert multiple rows on duplicate key update.

INSERT INTO `cloudx_wenjuan_luck`.`luckg_cross_sig`
(
`id`,
`survey_id`,
`bucket_key`,
`doc_count`,
`percent`,
`answer_total`,
`delete_at`)
VALUES (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?) ON DUPLICATE KEY
UPDATE
		`survey_id`=VALUES(survey_id),
		`bucket_key`=VALUES(bucket_key),
		`doc_count`=VALUES(doc_count),
		`percent`=VALUES(percent),
		`answer_total`=VALUES(answer_total),
		`delete_at`=VALUES(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at),(delete_at)