SELECT SCOPE_IDENTITY() to return the last inserted ID

Question

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:

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

Debugging tip: Try replacing SELECT SCOPE_IDENTITY() with select 42; and making sure that works. — HABO
– HABO, Commented Dec 26, 2019 at 3:58
Just tried that, it returns 0. In fact, while trying that I've noticed that while running a SELECT query, rs object remains open until it goes out of scope. When I run the INSERTquery, it immediately gets closed after executing the first query. — SercioSoydanov
– SercioSoydanov, Commented Dec 26, 2019 at 5:21
Try running the sql statement directly in SSMS. If you get back the correct value, you know for sure the problem is with the VBA side (which is the most likely bet even before that test). — Zohar Peled
– Zohar Peled, Commented Dec 26, 2019 at 5:45
@ZoharPeled I've already did that. That was, in fact, the first thing I've tried. Question states this detail. — SercioSoydanov
– SercioSoydanov, Commented Dec 26, 2019 at 5:48

HABO · Accepted Answer · 2019-12-26 16:26:59Z

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.

Great, I'll try it right away. For the cleanliness sake, can you update with a fully working example of a complete insert statement, for a very simple sample table composed of two columns, such as id and name?
@SercioSoydanov As requested. I've also assumed that it is a parameterized query with @Name passed in. Your query may vary.
I've tried it. If a table has triggers attached to it you still have to advance through recordsets to reach the result of the desired query, as described in my answer. But this info was so valuable for me and your answer is really useful, so I've accepted your answer.
Only thing that would be great to have, if you be so kind, is if you can emphasize this In your answer, so others can benefit from it too, in case they skip reading my answer. Thanks a lot.
@SercioSoydanov Okay, I'll bite. Triggers don't usually return recordsets as results, they just quietly validate, update or log data in the background. What are your triggers doing that produces output? What is supposed to be using the data, or are they just "leaking" data that was handy for debugging?

SercioSoydanov · Accepted Answer · 2019-12-26 12:24:24Z

As it turns out, the table that I'm trying to insert to has multiple triggers attached to it. So, the query which includes the SELECT SCOPE_IDENTITY(); is actually 4th query. I had to move 4 queries forward in order to get the correct scope. How I pulled that off programatically is as follows. Not very clean (and possibly not the best way to do it), but does the job for me.

I basically go ahead to next recordset until there is none left, which I detect by checking the error number 91 (Object variable or with block variable not set)

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 cQry.Query, pConn, adOpenKeyset, adLockOptimistic 'some error handling code which is not related to the issue On Error Resume Next 'begin loop Do 'go to next recordset Set rs = rs.NextRecordset() 'if we're getting error n. 91, it means 'recordsets are exhausted, hence we're getting 'out of the loop If Err.Number = 91 Then Err.Clear Exit Do End If 'if we are not out of recordsets, check the 'result of the query. If it is bigger then zero, 'it means we hit the jackpot. If rs.Fields(0).Value > 0 Then pInsertedId = rs.Fields(0).Value End If Loop On Error GoTo 0 End Function

Again, not the cleanest, nor the most correct way to do it, but did the trick. I'm open for any improvements or suggestions.

Collectives™ on Stack Overflow

SELECT SCOPE_IDENTITY() to return the last inserted ID

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related