How to insert multiple data at once

Question

I know that Insert multiple data at once more efficiency:

INSERT INTO test(n1, n2, n3) 
VALUES(v1, v2, v3),(v4, v5, v6),(v7, v8, v9);

How to do that in golang?

data := []map[string]string{
   {"v1":"1", "v2":"1", "v3":"1"},
   {"v1":"2", "v2":"2", "v3":"2"},
   {"v1":"3", "v2":"3", "v3":"3"},
}
//I do not want to do it
for _, v := range data {
    sqlStr := "INSERT INTO test(n1, n2, n3) VALUES(?, ?, ?)"
    stmt, _ := db.Prepare(sqlStr)
    res, _ := stmt.Exec(v["v1"], v["v2"], v["v3"])
}

Use string splice, but it's not good. db.Prepare more safer, right?

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES"
for k, v := range data {
    if k == 0 {
        sqlStr += fmt.Sprintf("(%v, %v, %v)", v["v1"], v["v2"], v["v3"])
    } else {
        sqlStr += fmt.Sprintf(",(%v, %v, %v)", v["v1"], v["v2"], v["v3"])
    } 
}
res, _ := db.Exec(sqlStr)

I need a function safer and efficient insert mulitple data at once.

Afghani

I'm not sure if MySQL supports this, but some SQL implementations support passing arrays as parameters to queries. If MySQL supports it, and whatever database driver you're using also supports it, you could do something like db.Exec("INSERT INTO test (n1, n2, n3) VALUES ?, ?, ?", []int{1, 2, 3}, []int{4, 5, 6}, []int{7, 8, 9}). You'll still have to construct the query string manually if you want to be able to handle arbitrary numbers of inserts (that is, you'll have to construct a query with "?, " repeated over and over), but it's better than nothing. – Man

Answer 1

why not something like this? (writing here without testing so there might be syntax errors):

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES "
vals := []interface{}{}

for _, row := range data {
    sqlStr += "(?, ?, ?),"
    vals = append(vals, row["v1"], row["v2"], row["v3"])
}
//trim the last ,
sqlStr = sqlStr[0:len(sqlStr)-1]
//prepare the statement
stmt, _ := db.Prepare(sqlStr)

//format all vals at once
res, _ := stmt.Exec(vals...)

Buskined

while trimming the last "," , why len(sqlStr)-2 but len(sqlStr)-1 ? – Exarchate

@DanielQiu len(sqlStr)-1 is the end index of the string, because in golang strings are zero based. so if you want to trim at the last "," you need -2 – Buskined

or sqlStr = strings.TrimSuffix(sqlStr, ",") – Lamarlamarck

@Buskined slicing does not include the value of end index so it should be sqlStr[0:len(sqlStr)-1] demo – Kassiekassity

To improve this already great solution, to avoid all the trimming clutter, rather than sqlStr += "(?, ?)," each time through the for loop, instead declare a const rowSQL= "(?, ?)" and a var inserts []string. Each time through the loop, append a rowSQL to inserts and when the loop is finished sqlInsert = sqlInsert + strings.Join(inserts, ",") – Advisedly

How to do something similar with postgres where need $1, $2, etc.? – Handclap

recommend strings.Join() to fix the , suffix. – Vinylidene

FYI, db.Exec() can take both sqlStr and vals... to prepare the statement.. i-e: Exec(sqlStr, vals...) – Macario

why did you use prepared statements in this case? – Citified

We need a defer stmt.Close() after Prepare(). See here – Vinylidene

Is this answer safe? Prepare() doesn't escape the string so this still gets raised by gosec as bad practice – Nodus

Answer 2

For Postgres lib pq supports bulk inserts: https://godoc.org/github.com/lib/pq#hdr-Bulk_imports

But same can be achieved through below code but where it is really helpful is when one tries to perform bulk conditional update (change the query accordingly).

For performing similar bulk inserts for Postgres, you can use the following function.

// ReplaceSQL replaces the instance occurrence of any string pattern with an increasing $n based sequence
func ReplaceSQL(old, searchPattern string) string {
   tmpCount := strings.Count(old, searchPattern)
   for m := 1; m <= tmpCount; m++ {
      old = strings.Replace(old, searchPattern, "$"+strconv.Itoa(m), 1)
   }
   return old
}

So above sample becomes

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES "
vals := []interface{}{}

for _, row := range data {
   sqlStr += "(?, ?, ?),"
   vals = append(vals, row["v1"], row["v2"], row["v3"])
}

//trim the last ,
sqlStr = strings.TrimSuffix(sqlStr, ",")

//Replacing ? with $n for postgres
sqlStr = ReplaceSQL(sqlStr, "?")

//prepare the statement
stmt, _ := db.Prepare(sqlStr)

//format all vals at once
res, _ := stmt.Exec(vals...)

Adolphadolphe

Answer 3

Gorm V2 (released on 30th August 2020) now supports batch insert query.

// Pass slice data to method Create, GORM will generate a single SQL statement
// to insert all the data and backfill primary key values,
// hook methods will be invoked too.

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
DB.Create(&users)

for _, user := range users {
  user.ID // 1,2,3
}

For more details refer to the official documentation here: https://gorm.io/docs/create.html.

Anele

Answer 4

If you enable multi statements , then you can execute multiple statement at once. With that , you should be able to handle multiple inserts.

https://github.com/go-sql-driver/mysql#multistatements

Pes

Allow multiple statements in one query. While this allows batch queries, ... Only the result of the first query is returned, all other results are silently discarded. This is for queries - but how does this behave for inserts? The documentation is quite sparse on this! – Goree

Answer 5

After extensive research this worked for me:

var values []interface{}
for _, scope := range scopes {
    values = append(values, scope.ID, scope.Code, scope.Description)
}
sqlStr := `INSERT INTO scopes (application_id, scope, description) VALUES %s`
sqlStr = setupBindVars(sqlStr, "(?, ?, ?)", len(scopes))

_, err = s.db.ExecContext(ctx, sqlStr, values...)

// helper function to replace ? with the right number of sets of bind vars

func setupBindVars(stmt, bindVars string, len int) string {
    bindVars += ","
    stmt = fmt.Sprintf(stmt, strings.Repeat(bindVars, len))
    return strings.TrimSuffix(stmt, ",")
}

Charmaincharmaine

Answer 6

If you're using Postgres there are a few ways to make the accepted answer work for you.

Directly adding the $ parameters holder is one way to do it if you're not using sqlx:

    query := `INSERT INTO test (n1, n2, n3) VALUES `
    args := []interface{}{}
    argNum := 0
    increArg := func() int { argNum += 1; return argNum }
    for _, row := range data {
        query += fmt.Sprintf("($%d, $%d, $%d),", increArg(), increArg(), increArg())
        args = append(args, row["v1"], row["v2"], row["v3"])
    }

if you're using Sqlx you can use the rebind function, which can either be called as a top-level sqlx function, or as a method in your database handle:

    query := `INSERT INTO test (n1, n2, n3) VALUES `
    args := []interface{}{}
    for _, row := range data {
        query += "(?, ?, ?),"))
        args = append(args, row["v1"], row["v2"], row["v3"])
    }
    query = query[0:len(query)-1]
    q := dbhandler.Rebind(query)
    ... execute or prepare statement

Khanna

Answer 7

this is an efficient way to do transition which will do network call only after commit.

func insert(requestObj []models.User) (bool, error) {
    tx := db.Begin()
    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        }
    }()

    for _, obj := range requestObj {
        if err := tx.Create(&obj).Error; err != nil {
            logging.AppLogger.Errorf("Failed to create user")
            tx.Rollback()
            return false, err
        }
    }
    err := tx.Commit().Error
    if err != nil {
        return false, err
    }
    return true, nil
}

Clump

Answer 8

I ended up with this, after combining the feedback on posted answers:

const insertQuery := "INSERT INTO test(n1, n2, n3) VALUES "
const row = "(?, ?, ?)"

var inserts []string 
vars vals []interface{}

for _, row := range data {
    inserts = append(inserts, row)
    vals = append(vals, row["v1"], row["v2"], row["v3"])
}
sqlStr := insertQuery + strings.Join(inserts, ",")

//prepare the statement
stmt, _ := db.Prepare(sqlStr)

//close stmt after use
defer stmt.Close()

//format all vals at once
res, _ := stmt.Exec(vals...)

Conti

Answer 9

From https://gorm.io/docs/create.html#Batch-Insert

Code sample:

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
DB.Create(&users)

Executory