accessing current written record

[Narayana Vyas Kondreddi]

See @@IDENTITY and SCOPE_IDENTITY( ) in SQL Server Books Online.

--
HTH,
Vyas, MVP (SQL Server)
[url]http://vyaskn.tripod.com/[/url]
What hardware is your SQL Server running on?
[url]http://vyaskn.tripod.com/poll.htm[/url]




"James Hall" <halljh@dshs.wa.gov> wrote in message
news:014301c33f3a$5cee73d0$a501280a@phx.gbl...
I am working on a multiple table insert. My master table
has an id field that is an identity field. Before writing
records to the related tables, I need to find out what the
id is that was just created. I seem to remember being able
to get to it by accessing a system cache - probably
tempdb. I can't find my example - anybody know how to do
this?

Thank you,
Jim.

[Drew Seale]

If you are performing a single insert you can use the SCOPE_IDENTITY()
function to retrun the last identity value inserted within the same scope
(proc, etc.)

Ex:
declare @ident int

INSERT ...

set @ident = SCOPE_IDENTITY()

--Drew


"James Hall" <halljh@dshs.wa.gov> wrote in message
news:014301c33f3a$5cee73d0$a501280a@phx.gbl...

> I am working on a multiple table insert. My master table
> has an id field that is an identity field. Before writing
> records to the related tables, I need to find out what the
> id is that was just created. I seem to remember being able
> to get to it by accessing a system cache - probably
> tempdb. I can't find my example - anybody know how to do
> this?
>
> Thank you,
> Jim.

[Aaron Bertrand - MVP]

DECLARE @id INT
INSERT tbl(col_list) VALUES(val_list)
SELECT @id = @@IDENTITY
PRINT @id


If using SQL Server 2000, replace @@IDENTITY with SCOPE_IDENTITY()

(It's always helpful to post the version of SQL Server you're using; the
solution can be different.)




"James Hall" <halljh@dshs.wa.gov> wrote in message
news:014301c33f3a$5cee73d0$a501280a@phx.gbl...

> I am working on a multiple table insert. My master table
> has an id field that is an identity field. Before writing
> records to the related tables, I need to find out what the
> id is that was just created. I seem to remember being able
> to get to it by accessing a system cache - probably
> tempdb. I can't find my example - anybody know how to do
> this?
>
> Thank you,
> Jim.