How to Batch SQL Statements with Package Database/Sql

How to batch SQL statements in Postgres using Golang

There is a bit old depesz blog post on that. His programs are Perl scripts, but if you concentrate on SQL... Anyway - from DB perspective, you can use COPY, or INSERT with many rows in VALUES. It looks that around 20 is good choice, but it is worth to test that in your case. If performance is key factor, I would put around 2000-5000 rows per transaction. Also, from DB perspective transaction, and session are two separate things. So you can open session, and to many transactions in it.

For PostgreSQL starting new session per operation is really bad idea - DB spawns new process for each session. One of answers for the question you referred contains this. So you open connection, and then transaction, as it should be done.

How to do a batch insert in MySQL

From the MySQL manual

INSERT statements that use VALUES
syntax can insert multiple rows. To do
this, include multiple lists of column
values, each enclosed within
parentheses and separated by commas.
Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Executing set of SQL queries using batch file?

Save the commands in a .SQL file, ex: ClearTables.sql, say in your C:\temp folder.

Contents of C:\Temp\ClearTables.sql

Delete from TableA;
Delete from TableB;
Delete from TableC;
Delete from TableD;
Delete from TableE;

Then use sqlcmd to execute it as follows. Since you said the database is remote, use the following syntax (after updating for your server and database instance name).

sqlcmd -S <ComputerName>\<InstanceName> -i C:\Temp\ClearTables.sql

For example, if your remote computer name is SQLSVRBOSTON1 and Database instance name is MyDB1, then the command would be.

sqlcmd -E -S SQLSVRBOSTON1\MyDB1 -i C:\Temp\ClearTables.sql

Also note that -E specifies default authentication. If you have a user name and password to connect, use -U and -P switches.

You will execute all this by opening a CMD command window.

Using a Batch File.

If you want to save it in a batch file and double-click to run it, do it as follows.

Create, and save the ClearTables.bat like so.

echo off
sqlcmd -E -S SQLSVRBOSTON1\MyDB1 -i C:\Temp\ClearTables.sql
set /p delExit=Press the ENTER key to exit...:

Then double-click it to run it. It will execute the commands and wait until you press a key to exit, so you can see the command output.

Creating generic code using database/sql package?

I assume this behaviour was left out specifically because SQL dialects vary significantly between databases, and the Go team wanted to avoid writing a preprocessor for each driver to translate 'GoSQL' into native SQL. The database/sql package mostly provides connection wrangling, which is an abstraction that falls under 'pretty much necessary' instead of statement translation, which is more 'nice to have'.

That said, I agree that re-writing every statement is a major nuisance. It shouldn't be too hard to wrap the database/sql/driver.Prepare() method with a regex to substitute the standard placeholder with the native one, though, or provide a new interface that specifies an additional PrepareGeneric method that guesses a wrapped sql.DB flavour, and provides similar translation.

Gorp uses a dialect type for this, which may be worth a look.

Just throwing out ideas.

Does a Go Mysql driver exist that supports multiple statements within a single string?

https://github.com/ziutek/mymysql

Can do it. Although you have to use its interface vs the go defined one. The go official interface doesn't handle it, or multiple return values.

package main

import (
    "flag"
    "fmt"

    "github.com/ziutek/mymysql/autorc"
    "github.com/ziutek/mymysql/mysql"
    _ "github.com/ziutek/mymysql/thrsafe"
)

type ScanFun func(int, []mysql.Row, mysql.Result) error

func RunSQL(hostport, user, pass, db, cmd string, scan ScanFun) error {
    conn := autorc.New("tcp", "", hostport, user, pass, db)

    err := conn.Reconnect()
    if err != nil {
        return err
    }

    res, err := conn.Raw.Start(cmd)
    if err != nil {
        return err
    }

    rows, err := res.GetRows()
    if err != nil {
        return err
    }

    RScount := 0
    scanErr := error(nil)

    for {
        if scanErr == nil {
            func() {
                defer func() {
                    if x := recover(); x != nil {
                        scanErr = fmt.Errorf("%v", x)
                    }
                }()
                scanErr = scan(RScount, rows, res)
            }()
        }

        if res.MoreResults() {
            res, err = res.NextResult()
            if err != nil {
                return err
            }
            rows, err = res.GetRows()
            if err != nil {
                return err
            }
        } else {
            break
        }

        RScount++
    }
    return scanErr
}

func main() {
    host := flag.String("host", "localhost:3306", "define the host where the db is")
    user := flag.String("user", "root", "define the user to connect as")
    pass := flag.String("pass", "", "define the pass to use")
    db := flag.String("db", "information_schema", "what db to default to")

    sql := flag.String("sql", "select count(*) from columns; select * from columns limit 1;", "Query to run")

    flag.Parse()

    scan := func(rcount int, rows []mysql.Row, res mysql.Result) error {
        if res.StatusOnly() {
            return nil
        }

        for idx, row := range rows {
            fmt.Print(rcount, "-", idx, ") ")
            for i, _ := range row {
                fmt.Print(row.Str(i))
                fmt.Print(" ")
            }
            fmt.Println("")
        }
        return nil
    }

    fmt.Println("Host - ", *host)
    fmt.Println("Db   - ", *db)
    fmt.Println("User - ", *user)

    if err := RunSQL(*host, *user, *pass, *db, *sql, scan); err != nil {
        fmt.Println(err)
    }
}