title: How do I return an auto generated ID using Slick plain SQL with SQL Server
body: <p>I would like to return the auto generated ID from an insert into a SQL Server table with an identity field</p>
  
  <p>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.</p>
  
  <p>i.e.)</p>
  
  <pre><code>val fooId = db.withSession((session: scala.slick.session.Session) =&gt; { 
    (sql"""
  INSERT INTO Foo VALUES ('bar')
  SELECT @@IDENTITY""").as[Int].first()(session)
  })
  </code></pre>
  
  <p>However, in this case slick is always returning 1.</p>
  
  <p>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 <em>needs</em> to return a scalar back in this case.</p>
  
  <pre><code>val fooIdTuple = db.withSession((session: scala.slick.session.Session) =&gt; { 
    (sql"""
  INSERT INTO Foo VALUES ('bar')
  SELECT @@IDENTITY as Foo, 47 as Bar""").as[(Int, Int)].first()(session)
  })
  </code></pre>
  
  <p>EDIT 2: This works, but I think it makes two round trips.  That probably means it could be victim to race conditions as well:</p>
  
  <pre><code>val fooId = db.withSession((session: scala.slick.session.Session) =&gt; { 
    (sql"""INSERT INTO Foo VALUES ('bar')""").as[Int].first()(session)
    (sql"""SELECT @@IDENTITY""").as[Int].first()(session)
  })
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>val fooId = db.withSession((session: scala.slick.session.Session) =&gt; { 
    (sql"""
  SET NOCOUNT ON
  INSERT INTO Foo VALUES ('bar')
  SET NOCOUNT OFF
  SELECT @@IDENTITY""").as[Int].first()(session)
  })
  </code></pre>
  
  <p>EDIT 4: This is the most concise solution.  It makes use of the <a href="http://technet.microsoft.com/en-us/library/ms177564.aspx" rel="noreferrer">OUTPUT feature</a> of SQL Server (thanks PB).</p>
  
  <pre><code>val fooId = db.withSession((session: scala.slick.session.Session) =&gt; { 
    (sql"""INSERT INTO Foo OUTPUT INSERTED.FooId VALUES ('bar')""").as[Int].first()(session)})
  </code></pre>