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

Question

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)})

An upvote for figuring out your own problem. :) – joescii Oct 02 '13 at 21:30 — joescii, Oct 02 '13 at 21:30

Sean Glover · Accepted Answer · 2013-10-03T14:15:24.987

1

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)})

edited Oct 03 '13 at 14:15

answered Oct 03 '13 at 14:03

Sean Glover

1,766
18
31

score 0 · Answer 2 · answered Oct 02 '13 at 20:39

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)
})

score 0 · Answer 3 · answered Feb 25 '14 at 10:03

0

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 ("theEmailAdress@test.de")
""".as[Int].firstOption

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

answered Feb 25 '14 at 10:03

OliverKK

505
4
15

I was using MSSQL, not MySQL. At the time I was working with plainSQL and slick there wasn't a solution. Although their MSSQL driver using their DSL (not plainSQL) does support it from what I've read. – Sean Glover Feb 26 '14 at 03:54
Thanks for the answere. Finally i found a way to retrieve the id with plainSQL. See here: http://stackoverflow.com/questions/22013105/how-to-retrieve-the-auto-incremented-id-using-slick-plainsql – OliverKK Feb 26 '14 at 08:54
Glad you figured it out. – Sean Glover Feb 26 '14 at 13:46

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

3 Answers3