CODEWITHSUNDEEP
sqlsql-servergo
r.sendecky
r.sendecky

Reputation: 10353

How to get last inserted ID with GO-MSSQLDB driver?

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.

Upvotes: 3

Views: 3686

Answers (2)

Kevin Bateman
Kevin Bateman

Reputation: 31

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.

Upvotes: 3

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

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)

Upvotes: 1

Related Questions