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…

Accepted Answer

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.

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

Solution

Problem