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()
}
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)
}
Adding to the accepted answer: I'd recommend you to do a few things more to ensure consistency,and handle your resources a bit more "responsibly". You are closing your DB connections, sure enough, but I'd get into the habit of treating the other resources in pretty much the same way.
Furthermore, if something goes wrong mid-way, instead of having to truncate the table and restart the process, I'd strongly recommend you wrap the inserts into a transaction:
defer rows.Close() // close your rows resource
defer db1.Close() // close at the very end
tx, err := db1.BeginTx(context.Background(), nil) // pass in a context with timeout or some other cancelable context if needed
if err != nil {
return err // I'm returning, you can do as you like with this
}
stmt, err := tx.Prepare("INSERT INTO ...") // create the statement once
defer stmt.Close() // add to defer stack
for rows.Next() {
// instead of your Camel_Snake_Cased vars, I'm using the preferred style and camelCase your var names instead...
if err := rows.Scan(&incidentNumber, &lastResolvedDate, &corporateID); err != nil {
tx.Rollback() // rollback transaction
return err
}
if err := stmt.Exec(incidentNumber, lastResolvedDate, corporateID); err != nil {
tx.Rollback() // again, rollback...
return err
}
}
if err := tx.Commit(); err !≃ nil {
tx.Rollback() // not needed, but I add it as a habit
return err
}
return nil
Now it's important to note that all those Close() functions, and the tx.Rollback() call all return errors. It's always best to at least wrap them in a log call like so:
log.Printf("Rolling back: %+v", tx.Rollback())
// and
log.Printf("Closing DB/ROWS/STMT: %+v", db1.Close()) // same for rows, stmt etc..