Insert a new row and get the auto generated id in the same call

[ndjordjevic]

Hi,

Can someone post an example how to do this?

[chris-rossi]

I can work on this.

[ndjordjevic]

I switched to minus5/gofreetds driver, installed freetds and LastInsertId() was returning the correct last generated id... Then I switched back to denisenkom/go-mssqldb and magically LastInsertId() started to work... Now I'm totally confused ... :) Before I haven't had a freetds installed.. Ok uninstalled freetds and LastInsertId() is still working :) No idea what's going on :)

[chris-rossi]

I have no idea what your issue was, but I'm glad to hear it's working. I'll still work on creating an example for this scenario.

[kardianos]

Please be aware that there is no 100% correct way with SQL server to return a LastInsertID without modifying the SQL and returning it in the select statement.

If you are using SQL Server DO NOT USE OR RELY on LastInsertID.

You should use something like:

insert into mytable (mytextcol)
ouput inserted.ID
values (N'myval');

[ndjordjevic]

Alright I'll explain what i'm doing

1. First I used sql.DB.Exec() to insert the row and tried to extract the generated id with a result. LastInsertId(). That doesn't work as stated at https://github.com/denisenkom/go-mssqldb#important-notes
2. Then I tried what is suggested there by adding SELECT ID = convert(bigint, SCOPE_IDENTITY()); along with the INSERT in Exec(). It can't be done like that...
3. Then I switched to DB.QueryRow() and passed both INSERT and SELECT ID =... to it and then Scan()... It works but if there is an error while Insertion that error will be hidden ...
4. Then I switched to sql.Tx.Exec() and magically LastInsertId() started to return an id

[kardianos]

Not magical, each connection is a unique session. calling db.Conn(), then calling exec then scope_identity on the same connection is what you want to do. Making a Tx is another way to do that.

[ndjordjevic]

Please be aware that there is no 100% correct way with SQL server to return a LastInsertID without modifying the SQL and returning it in the select statement.

If you are using SQL Server DO NOT USE OR RELY on LastInsertID.

You should use something like:

insert into mytable (mytextcol)
ouput inserted.ID
values (N'myval');

I see but how to execute this? With a db.Exec or with something else? db.Exec doesn't work.

[kardianos]

There is no such thing as "exec" in SQL Server. Exec for the DB interface just means disregard all rows. Don't use Exec. Use Query. Scan the first row to get output.

[scbizu]

The error will also be hidden if I replace the SCOPE_IDENTITY() solution with OUTPUT clause. However, when I try to insert some duplicate rows to a table:

• SCOPE_IDENITY() will return the driver type conversion error between null and int64
• OUTPUT clause do not return any errors

They both do not actually insert the duplicate rows into the table .

Any suggestion about how to capture the raw database error when insert duplicated rows ?

@kardianos

[yukiwongky]

@scbizu What is your table schema? What is the query you're running? Why can't duplicate rows be insert into the table (I'm guessing there is a unique column in your table)?

I tried both using SCOPE_IDENTITY() and OUTPUT I was able to get the correct error (mssql: Violation of UNIQUE KEY constraint 'UQ__foo__DE90ECF660F04C30'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate key value is (1).).

The following is the queries I tried to run:

rows, err := db.Query("insert into foo (baz) values (1); select ID = convert(bigint, SCOPE_IDENTITY())")

and

err = db.QueryRow("insert into foo (baz) output inserted.bar values (1)").Scan(&lastInsertId2)

where baz is a unique column.

[scbizu]

@v-kaywon Are you sure the first case error is the duplicated error ? I got the conversion error even if I use the db.Query() (or db.QueryRow()), it says sql: Scan error on column index 0, name \"ID\": converting driver.Value type <nil> (\"<nil>\") to a int64: invalid syntax. The code looks likes below:

rows, err := db.Query("MY INSERT QUERY; select ID = convert(bigint, SCOPE_IDENTITY())")
if err != nil {
  return err
}
defer rows.Close()

if !rows.Next() {
  if err:= rows.Err();err!=nil{
    return err
  }
  return INSERTID_NOT_FOUND_ERROR
}

var id int64

err := rows.Scan(&id); err!=nil {
   return err
}

As for output clause, I fixed up my scan bug , And now it works as expected.

Anyway, Thanks for your help.

[yukiwongky]

I doubled checked and tried again with the query "insert into foo (baz) values (1); select ID = convert(bigint, SCOPE_IDENTITY())" and I do get the violation of unique key constraint error. I'm not sure why it's not working for you.. Anyway I'm glad the output query works for you.

[Fs02]

Hi, I'm also having this problem, and able to reproduce using this code:

code modified from: https://github.com/denisenkom/go-mssqldb/issues/23#issuecomment-42204645

func TestDriver(t *testing.T) {
	db, err := sql.Open("mssql", dsn())
	fmt.Println(db, err)
	fmt.Println(db.Ping())
	defer db.Close()
	schema := "CREATE TABLE [dbo].[Foo3]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](100) NOT NULL,CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],CONSTRAINT [IX__Name] UNIQUE NONCLUSTERED ([Name] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"
	_, err = db.Exec(schema)
	fmt.Println(err)

	// first record, return nil is expected
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?)", "foo")
	fmt.Println("first record: ", err) //nil

	// duplicate record, error returned
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate record no id returned: ", err) // mssql: Violation of UNIQUE KEY constraint

	// duplicate record with output inserted, no error returned???
	_, err = db.Query("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate with inserted: ", err) // nil??

	// duplicate record with output inserted, no error returned???
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?); select ID = convert(bigint, SCOPE_IDENTITY())", "foo")
	fmt.Println("duplicate with select id: ", err) // nil??

	// works: error returned
	_, err = db.Query("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID " +
		"VALUES ('foo')")
	fmt.Println("duplicate with inserted, literal only: ", err) //Real error!

	// duplicate record with output inserted, no error returned???
	_, err = db.Exec("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate with inserted using exec: ", err) // nil??

	// duplicate record with output inserted, no error returned???
	_, err = db.Exec("INSERT INTO Foo3([Name]) "+
		"VALUES (?); select ID = convert(bigint, SCOPE_IDENTITY())", "foo")
	fmt.Println("duplicate with select id using exec: ", err) // nil??

}

output:

=== RUN   TestDriver
&{0 {sqlserver://sa:REL2021-mssql@localhost:1433?database=master&log=32 0x17ff040} 0 {0 0} [] map[] 0 0 0xc00008e480 0xc00015cf00 false map[] map[] 0 0 0 <nil> 0 0 0 0x10dcfa0} <nil>
<nil>
<nil>
first record:  <nil>
duplicate record no id returned:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
duplicate with inserted:  <nil>
duplicate with select id:  <nil>
duplicate with inserted, literal only:  <nil>
duplicate with inserted using exec:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
duplicate with select id using exec:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
--- PASS: TestDriver (0.19s)

it seems db.Query can't detect if constraint error is thrown?