如何使用GO-MSSQLDB驱动程序获取最后插入的ID？

I gathered that SQL Server does not return last inserted id automatically and I need to do it manually with: OUTPUT INSERTED.ID within SQL insert statement.

How do I pick it up later in Go code?

The function in question is:

    func (sta *state) mkLogEntry(from time.Time, to time.Time, man bool) (id int64) {
    qry := "INSERT INTO ROMEExportLog(FromDate,ToDate,ExecutedAt,ExecutedManually,ExportWasSuccessful,UpdatedDaysIrregular) OUTPUT INSERTED.ID " +
        "VALUES(@FromDate,@ToDate,@ExecutedAt,@ExecutedManually,@ExportWasSuccessful,@UpdatedDaysIrregular)"
    res, err := sta.db.Exec(qry,
        sql.Named("FromDate", from),
        sql.Named("ToDate", to),
        sql.Named("ExecutedAt", time.Now()),
        sql.Named("ExecutedManually", man),
        sql.Named("ExportWasSuccessful", false),
        sql.Named("UpdatedDaysIrregular", false),
    )
    if err != nil {
        log.Fatal(err)
    }
    id, err = res.LastInsertId()

    if err != nil {
        log.Fatal(err)
    }
    return
}

The res.LastInsertId() returns There is no generated identity value.

The reason for this is because PostgreSQL does not return you the last inserted id. This is because last inserted id is available only if you create a new row in a table that uses a sequence.

If you actually insert a row in the table where a sequence is assigned, you have to use RETURNING clause. Something like this: INSERT INTO table (name) VALUES("val") RETURNING id.

I am not sure about your driver, but in pq you will do this in the following way:

lastInsertId := 0
err = db.QueryRow("INSERT INTO brands (name) VALUES($1) RETURNING id", name).Scan(&lastInsertId)

FOR SQL SERVER:

This may be gimmicky... But I found that using QueryRow will run multiple queries/commands and just return the LAST row.

if err := db.QueryRow(`
    INSERT INTO [TABLE] ([COLUMN]) VALUES (?); 
    SELECT SCOPE_IDENTITY()`, 
    colValue
).Scan(&id); err != nil {
    panic(err)
}

As long as SELECT SCOPE_IDENTITY() is the last row returned then this essentially does what I would have expected result.LastInsertId() to do.