Menu
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.
460
Insertion in a table is a DML (Data manipulation language) operation in SQL. When we want to store data we need to insert the data into the database. We use the INSERT statement to insert the data into the database.
If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
To insert multiple rows, select the same number of rows that you want to insert. To select multiple rows hold down the "shift" key on your keyboard on a Mac or PC. For example, if you want to insert six rows, select six rows while holding the "shift" key.
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...) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
