How to return an automatically generated identifier using Slick plain SQL with SQL Server

Question

I would like to return an automatically generated identifier from an insert in a SQL Server table with an identification field

My first attempt, which returns a single result set for a row / column with an identifier, was to use @@ IDENTITY SQL Server functionality with my insert statement.

i.e.)

val fooId = db.withSession((session: scala.slick.session.Session) => { (sql""" INSERT INTO Foo VALUES ('bar') SELECT @@IDENTITY""").as[Int].first()(session) }) 

However, in this case, slick always returns 1.

EDIT: I lied, it always returns 1, and I think this is the number of rows affected. As a test, I tried to modify the query to see what happens, and I got the exception "java.lang.ClassCastException: java.lang.Integer cannot be attributed to scala.Tuple2". It seems that in this case he needs to return the scalar.

 val fooIdTuple = db.withSession((session: scala.slick.session.Session) => { (sql""" INSERT INTO Foo VALUES ('bar') SELECT @@IDENTITY as Foo, 47 as Bar""").as[(Int, Int)].first()(session) }) 

EDIT 2: It works, but I think he makes two round trips. This probably means that he may fall prey to race conditions:

 val fooId = db.withSession((session: scala.slick.session.Session) => { (sql"""INSERT INTO Foo VALUES ('bar')""").as[Int].first()(session) (sql"""SELECT @@IDENTITY""").as[Int].first()(session) }) 

EDIT 3: The more I think about it, the more clear to me that, given my use of the spot, this is not a very subtle topic, but rather a SQL Server and JDBC question. This is my last decision.

 val fooId = db.withSession((session: scala.slick.session.Session) => { (sql""" SET NOCOUNT ON INSERT INTO Foo VALUES ('bar') SET NOCOUNT OFF SELECT @@IDENTITY""").as[Int].first()(session) }) 

EDIT 4: This is the shortest solution. It uses the OUTPUT function for SQL Server (thanks PB).

 val fooId = db.withSession((session: scala.slick.session.Session) => { (sql"""INSERT INTO Foo OUTPUT INSERTED.FooId VALUES ('bar')""").as[Int].first()(session)}) 

Answer 1

Given my use of slick (plain SQL), this is not a very popular topic, but rather a SQL Server question.

This is the most concise solution. It uses the OUTPUT function for SQL Server (thanks PB).

 val fooId = db.withSession((session: scala.slick.session.Session) => { (sql"""INSERT INTO Foo OUTPUT INSERTED.FooId VALUES ('bar')""").as[Int].first()(session)}) 

Answer 2

Just in the dark ... Does the semicolon after the INSERT add a problem?

 val fooId = db.withSession((session: scala.slick.session.Session) => { (sql""" INSERT INTO Foo VALUES ('bar'); SELECT @@IDENTITY""").as[Int].first()(session) }) 

Answer 3

If I use your example, errors occur. userId is an automatic incremental field in my mySQL table.

 sql""" INSERT INTO `table`(`email`) OUTPUT INSERTED.userId VALUES (" [e-mail] ") """.as[Int].firstOption 

Is there a native slick / plainSQL solution to get the automatically incremented identifier of the current INSERT?