scope_identity() and the identity of inserted records

SteveCooperOrg

When inserting a record in a normal table, which has an auto-number ID column, you'd expect to be able to do this;

INSERT ...

INTO [tableName]

SELECT scope_identity()

And for that to show the identity of the just-inserted record. However, if you insert into an l-view, scope_identity() returns null.

I'm trying to adapt Anchor Modeling to work with Entity Framework, and EF is generating some code like that above. It uses that so it can track the ID of the element you just added.

Is there a good way to get the ID of the recently-inserted element? I think a select statement like "(select top(1) RO_ID from lRO_Role order by RO_ID) desc" would not necessarily be thread-safe -- that is, if I'm inserting at the same moment as someone else, I don't know that I'd get my ID back and not theirs.

Any help appreciated...

Roenbaeck

I haven't verified this, but it should be possible using an OUTPUT clause in your insert statement:

http://technet.microsoft.com/en-us/library/ms177564.aspx

Your query would look something like this:

INSERT INTO [latestView] OUTPUT inserted.[identityColumn] VALUES|SELECT ...