检索后无法将数据插入数据库

Question

Need help extracting data from database ms sql and add them to the database postgres. The data extraction is successful. But with the insert problem. I can't break the interface down into 18.

package main

import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "log"

    _ "github.com/denisenkom/go-mssqldb"
    _ "github.com/lib/pq"
)

const (
// config to connect to ms sql server

// config to connect to PostgreSQL

)

func main() {
    connStringmssql := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;", server, user, password, port, database)
    db, err := connect("mssql", connStringmssql)
    checkError("Error mssql connect function ", err)
    defer db.Close()
    defer fmt.Printf("Disconnected mssql!
")

    connStringpg := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s", serverpg, portpg, userpg, passwordpg, databasepg, "disable")
    dbpg, err := connect("postgres", connStringpg)
    checkError("Error connect function pg ", err)
    defer dbpg.Close()
    defer fmt.Printf("Disconnected pg!
")

    // Read sql query from file and convert it
    mssqlSelectQueryUnConvert, err := ioutil.ReadFile("./sql/mssqlSelect.sql")
    checkError("Error read sql file", err)
    mssqlSelectQuery := string(mssqlSelectQueryUnConvert)

    rows, err := db.Query(mssqlSelectQuery)
    checkError("Error in sql query ", err)
    defer rows.Close()
    columnNames, err := rows.Columns()
    checkError("Error sql columns names ", err)

    objects := make([]interface{}, len(columnNames))
    objectsScan := make([]interface{}, len(columnNames))
    rest := make([]interface{}, len(columnNames))
    for i := range objects {
        objectsScan[i] = &objects[i]
    }

    for rows.Next() {
        err = rows.Scan(objectsScan...)
        if err != nil {
            fmt.Println("Failed to scan row", err)
            return
        }
        rest = append(rest, objects...)
    }
    fmt.Println(rest)

    dbInsert, err := dbpg.Exec("INSERT INTO object_test (column1...column18) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)", rest...)
    checkError("Error dbpg insert ", err)
    fmt.Println(dbInsert.RowsAffected())
}

I get error: Error dbpg insert pq: got 54 parameters but the statement requests 18

Where did I go wrong? Thanks.

Answer 1

You're joining all of your rows into a single rest variable, then trying to insert them all into a single row in Postgres. This obviously won't work.

You probably need to move your db.Insert statement into your rows.Next() loop, and do one insert per row.