Posted by Erland Sommarskog on 03/09/06 01:04

pb648174 (google@webpaul.net) writes:
> In a multi-user environment, I would like to get a list of Ids
> generated, similar to:
>
> declare @LastId int
> select @LastId = Max(Id) From TableMania
>
> INSERT INTO TableMania (ColumnA, ColumnB)
> SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
>
> --get entries just added
> SELECT * FROM TableMania WHERE Id > @LastId
>
>
> The above works fine, except I'm assuming it will not work in a
> multi-user environment. Is there any way to get the set of Ids that
> were just added in the previous statement (similar to @@IDENTITY)
> without doing all of this in a serializable transaction or making a
> temp table of every single Id before the insert statement?

Actually, I don't know.

Say that you insert a couple of rows into a table with a column ident
that has the IDENTITY property. @@identity or scope_identity gives
you the highest value for ident for the lnserted rows. But is the
lowest value @@identity - @@rowcount + 1?

I have never seen any documentation that guarantees this to be true.
It is likely to be, but what if you insert 10000 rows, and in the
middle of this another users needs to insert a single row. Will he
steal a value?

One strategy would be to retrieve ident_current() before the insertion (or
MAX(ident), and then after the insertion check that the interval is equal
to @@rowcount, and bail out if it's not.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]