How do I return an auto generated ID using Slick plain SQL with SQL Server

7
votes

I would like to return the auto generated ID from an insert into a SQL Server table with an identity field

My first attempt, which returns a single row/column result set with the ID, was to use the @@IDENTITY functionality of SQL Server along 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 is always returning 1.

EDIT: I lied, it's always returning 1, which I think is the number of rows affected. As a test I tried modifying the query like so to see what would happen and I received an exception "java.lang.ClassCastException: java.lang.Integer cannot be cast to scala.Tuple2". It looks like it needs to return a scalar back in this case.

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: This works, but I think it makes two round trips. That probably means it could be victim to race conditions as well:

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 this the more clear to me that given my usage of slick it's not really a slick topic, but more of a SQL Server and JDBC question. This is my latest solution.

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 most concise solution. It makes use of the OUTPUT feature of 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)})
3
sql-serverscalajdbcslick
An upvote for figuring out your own problem. :) – joescii

3 Answers

1
votes

Given my usage of slick (plain SQL) this is not really a slick topic, but more of a SQL Server question.

This is the most concise solution. It makes use of the OUTPUT feature of 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)})
0
votes

Just a shot in the dark... Does adding a semicolon after the INSERT statement resolve the issue?

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

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

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

Isn't there a slick/plainSQL native solution to retrieve the auto incremented id of the current INSERT?