Search code examples
excelvbat-sqladodbscope-identity

SELECT SCOPE_IDENTITY() to return the last inserted ID

There are a few answers about this problem, but my question is about the particular code I have.

I'm trying to get the last inserted ID of this query executing on VBA code.

Public Function Execute(cQry As excfw_dbQuery) As ADODB.Recordset    

    If pConn.State = 0 Then
        OpenConnection
    End If

    qry = "INSERT INTO [some really long query, which actually works]; SELECT SCOPE_IDENTITY()"

    On Error Resume Next
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open qry, pConn 'also tried with adOpenKeyset, adLockOptimistic

    'some error handling code which is not related to the issue 

    Set rs = rs.NextRecordset() 'also tried without moving onto the next recordset
    pInsertedId = rs.Fields(0).Value

    Set Execute = rs 'this is just to pass the query result for SELECT queries 

End Function

This should save the last inserted ID on the pInsertedId variable, but instead I get 0 each time I insert a row. The weird thing is, when I copy and paste the same code into the SSMS, it works.

I might just get away with inserting some unique data to some unused column of the database and querying through that.

--UPDATE--

I've just noticed that when running a SELECT query, rs object remains open until it goes out of scope. Here is a screenshot of the watch section:

select statement

on an insert statement instead, it gets closed as soon as the query gets executed:

insert statement

Solution

  • You can explicitly save the results of the insert statement by using an output clause and return the results with a select:

    qry =
  "declare @Ids as ( Id Int );" +
  "insert into MyTable ( Name ) " + ' Assuming   Id   is an identity column.
     "output Inserted.Id into @Ids " + 
     "values ( @Name );" +
  "select Id from @Ids;"

    From the documentation for output:

    INSERTED Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

    You can use an output clause to get any data from the rows (Note plural.), e.g. identity column values for newly inserted rows. Output can be used with insert, update, delete and merge and provides access to both before and after values in the case of update. A tool well worth having in your pocket.