insert record into two joined tables

[MarkyMark]

This is on SQL Server 2000, I don't think it's possible, but maybe there is some way of doing it, or another better idea? If I have two tables, and I want to insert a new record into both of them, and at the same time set a field in one table to be the identity inserted field in the other, can that be done? e.g. the SQL I'd want to work would be for example: insert into tablea inner join tableb on tablea.bid = tableb.id( a, b, c, bid ) values( 1, 2, 3, tableb.id)

[mr_12345]

use a stored procedure.

[MarkyMark]

OK, thanks, that's a good idea. But the question still remains about the SQL to use in the stored procedure.

[Mike Dimmick]

You can retrieve the value of the identity column from the last insert performed using the @@IDENTITY variable. Due to the action of triggers, however, this can be the wrong value if one or more triggers that fired also performed an insert. On SQL Server 2000 and later the SCOPE_IDENTITY() function is a better choice. So something like:

INSERT INTO tableb( c )
VALUES( 3 )

INSERT INTO tablea( a, b, bid )
VALUES( 1, 2, SCOPE_IDENTITY() )

Stability. What an interesting concept. -- Chris Maunder

[MarkyMark]

That's great, thanks Mike!