Inserting row with foreign key relation in same transaction as primary row

Question

I have a 2 tables where one has a foreign key relation to the other

CREATE TABLE foo (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  value VARCHAR(50) DEFAULT NULL,
);

CREATE TABLE bar (
  id INT NOT NULL PRIMARY KEY IDENTITY,
  foo_key INT NOT NULL
  value VARCHAR(50) DEFAULT NULL,
);

I'm using parameterized ADO.NET ExecuteReader to Insert new rows. My pickle is, if I want to insert 2 rows in different tables in the same transaction, i.e. before commit, I cannot insert rows in bar since I don't know the value that has been given foo.id yet. How would you go about doing that? i.e. How do I make sure that bar.foo_key get assigned the right value? Trying to select on it brings nothing, since I guess it is not actually there yet. Should I use a stored procedure to try and generate the key on the fly, or maybe there is an internal variable that can be used. Or is there a way to have the insert return the new id? Do I need a foreign key declaration, though I'm not sure that would be useful since again I still don't know what id to use?

The reason why I want to do it in one go, is due to error handling, I want to be able to roll everything back in case of an error.

Answer 1

You can use scope_identity() to retrieve the newly generated identity:

begin tran;
insert Foo (value) values ('6*7');
declare @fk int = scope_identity();
insert bar (foo_key, value) values (@fk, '42');
commit tran;

Per HLGEM's comment, to return the value of the newly generated identity to the client, you can use output:

insert Foo (value) output inserted.ID values ('6*7');

Note that for a transaction to span two sessions, you need a distributed transaction, which is very expensive.