Last Identity Value

IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
I need the last identity generated for a specific table for the current
session. Looks like I can do one or the other, but not both. Or is there a
way to get it?It shouldn't be a problem. Within the session scope you just need to
preserve the IDENTITY value after the INSERT to the appropriate table.
In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
Example:
INSERT INTO table1 (...)
..
SET @.t1 = SCOPE_IDENTITY()
INSERT INTO table2 (...)
..
SET @.t2 = SCOPE_IDENTITY()
David Portas
SQL Server MVP
--|||If the table has a "before insert" trigger on it scope_identity wont work.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130235284.897816.213730@.g14g2000cwa.googlegroups.com...
> It shouldn't be a problem. Within the session scope you just need to
> preserve the IDENTITY value after the INSERT to the appropriate table.
> In SQL Server 2000 SCOPE_IDENTITY is usually preferred to @.@.IDENTITY
> because SCOPE_IDENTITY is unaffected by INSERTs in triggers.
> Example:
> INSERT INTO table1 (...)
> ...
> SET @.t1 = SCOPE_IDENTITY()
>
> INSERT INTO table2 (...)
> ...
> SET @.t2 = SCOPE_IDENTITY()
> --
> David Portas
> SQL Server MVP
> --
>|||If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
that instance.
See: http://www.aspfaq.com/show.asp?id=2174
David Portas
SQL Server MVP
--|||Unless you're also dumping data into a Audit table which has an Identity
column.
Then you're up the proverbial ;)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
> If you mean an INSTEAD OF trigger then you are right. Use @.@.IDENTITY in
> that instance.
> See: http://www.aspfaq.com/show.asp?id=2174
> --
> David Portas
> SQL Server MVP
> --
>|||Then you have to look up the value using the alternate key--which I prefer
because it works with both set-based and single-row inserts. It's also
necessary to issue a select to obtain the current rowversion (timestamp), so
there isn't any additional overhead involved in obtaining the IDENTITY
values (provided you're using optimistic concurrency with rowversioning).
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435e3382$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> Unless you're also dumping data into a Audit table which has an Identity
> column.
> Then you're up the proverbial ;)
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1130247300.970289.319770@.g14g2000cwa.googlegroups.com...
>