How can you tell what the primary key of a new row will be?

I need to insert a row into a table in SQL Server 2000. The primary
key for the row is an identity type, so it auto-numbers for me without
needing to put in the value in the insert statement.

My problem, is that after i insert a row, i need to insert another row
in a different table that references the first row. To do that i need
to know the primary key for the original row.

How can i tell what the primary key was? In Oracle, you would check
the sequence before the original insert. Is there a similar feature
in SQL Server? And how would you use it?

(I'm using C# ADO)

- PaulOn 7 Jan 2004 17:26:13 -0800, prempel@.paradata.com (Paul) wrote:

>I need to insert a row into a table in SQL Server 2000. The primary
>key for the row is an identity type, so it auto-numbers for me without
>needing to put in the value in the insert statement.
>My problem, is that after i insert a row, i need to insert another row
>in a different table that references the first row. To do that i need
>to know the primary key for the original row.
>How can i tell what the primary key was? In Oracle, you would check
>the sequence before the original insert. Is there a similar feature
>in SQL Server? And how would you use it?
>(I'm using C# ADO)
>- Paul

In SQL Server, you check the identity value immediately following the INSERT.
The old way to do this was to check the @.@.IDENTITY variable, but that's
unrelibale if a trigger also inserts a row into another table, so the new,
preferred way to do it is the SCOPE_IDENTITY function.|||Paul wrote:

> I need to insert a row into a table in SQL Server 2000. The primary
> key for the row is an identity type, so it auto-numbers for me without
> needing to put in the value in the insert statement.
> My problem, is that after i insert a row, i need to insert another row
> in a different table that references the first row. To do that i need
> to know the primary key for the original row.
> How can i tell what the primary key was? In Oracle, you would check
> the sequence before the original insert. Is there a similar feature
> in SQL Server? And how would you use it?
> (I'm using C# ADO)
> - Paul

I'll leave it to someone that knows more about SQL Server than I to
answer your question. But what you suggest for Oracle doesn't work in
Oracle. Well unless you are in a single-user environment. The solution
in Oracle would be to use the RETURNING clause of the INSERT statement
as in:

DECLARE
x emp.empno%TYPE;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING empno
INTO x;

dbms_output.put_line(x);
END;
/

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)