如何执行插入查询，其中参数数据来自选择查询的结果集

Question

I am trying to fetch data from one database(db) which is in a different server and insert the fetched data to another database(db1) in a different server. So far I am able to select data from db but could not insert it into db1. I am getting Null values in the database. Any help would be appreciated.

My code snippet is below:

func searchHandler(w http.ResponseWriter, r *http.Request) {
dsn := "server=*****.ecc4q.****.com; user id=******; password=***************"
db, err := sql.Open("mssql", dsn) 
if err != nil {
    log.Fatal(err)
}
err = db.Ping()
if err != nil {
    log.Fatal(err)
}


rows, err := db.Query("select Incident_Number, Last_Resolved_Date, Corporate_ID from ITSM.dbo.HPD_Help_Desk_Classic")
if err != nil {
    log.Println(err)
}
defer db.Close()

dsn1 := "server=******.md3q.***.com;user id=*****;password=********"
db1, err := sql.Open("mssql", dsn1) 
if err != nil {
    log.Fatal(err)
}
err = db1.Ping()
if err != nil {
    log.Fatal(err)
}

for rows.Next() {
    err := rows.Scan(&Incident_Number, &Last_Resolved_Date, &Corporate_ID)
        if err != nil {
            log.Fatal(err)
        }

    stmt1, err := db1.Prepare("INSERT INTO mdesk.dbo.tbl_tcktinfo(TicketNumber, ResolvedDate, CDSID) VALUES(?,?,?)")
        if err != nil {
            log.Println(err)
        }

    res, err := stmt1.Exec(TicketNumber, ResolvedDate, CDSID)
        if err != nil {
            log.Println(err)
        }
    log.Println(Incident_Number, Last_Resolved_Date, Corporate_ID)
}
defer db1.Close() 

}

Answer 1

You scan records into Incident_Number, Last_Resolved_Date, Corporate_ID from rows like:

err := rows.Scan(&Incident_Number, &Last_Resolved_Date, &Corporate_ID)

But you pass different argument in Exec() function. You have to pass scaned variables to insert records into different database table.

stmt1, err := db1.Prepare("INSERT INTO mdesk.dbo.tbl_tcktinfo(TicketNumber, ResolvedDate, CDSID) VALUES(?,?,?)")
if err != nil {
    log.Fatal(err)
}
defer stmt1.Close() // always clean up after yourself, preferably return errors here, too

for rows.Next() {
    err := rows.Scan(&Incident_Number, &Last_Resolved_Date, &Corporate_ID)
        if err != nil {
            log.Fatal(err)
        }


    res, err := stmt1.Exec(Incident_Number, Last_Resolved_Date, Corporate_ID)
        if err != nil {
            log.Println(err)
        }
    log.Println(Incident_Number, Last_Resolved_Date, Corporate_ID)
}