INSERT into table and UPDATE foreign key in another table (Sql Server 2008)

Question

I have two tables, TableA and TableB like this:

TableA
-------------------------------------------
|  id  |  some_data  | new_FK_column_on_B |
| ---- | ----------- | ------------------ |
|   1  |     ...     |        null        |
|  ... |     ...     |        null        |
|  999 |     ...     |        null        |
-------------------------------------------

TableB
----------------------------
|  id  |  some_other_data  |
| ---- | ----------------- |
|      |                   |
----------------------------

At the moment, TableB is empty, and FK column in TableA is null for all rows. I need to write one-time initializing scrit to populate TableB and initialize FK column for some rows (criterial, not for all) in TableA by identifiers from rows, inserted in TableB.

I know two ways to do this:

1) using while and scope_identity(), inserting new row into TableB and updating TableA on each iteration, while exists rows in TableA, which should be updated

while (exists (select 1 from TableA where [condition]))
begin
    insert into TableB (some_other_data) values ('some_other_data')

    update TableA set new_FK_column_on_B
    where id = (select top 1 id from TableA where [condition])
end

2) create temp column in TableB, storing id of row in TableA, for which it was inserted, and then update TableA using join

alter table TableB add temp int
go

insert into TableB (some_other_data, temp) select 'some_other_data', id from TableA where [condition]

update TableA
set new_FK_column_on_B = b.id
from TableB as b
join TableA as a on a.id = b.temp

alter table TableB drop column temp

Also I was trying to use somehow output from insert like this, but it's syntax is incorrect:

update TableA
set new_FK_column_on_B =
(
    select insertedId from 
    (
        insert into TableB (some_other_data)
        output inserter.id as insertedId
        values ('some_other_data')
    )
)
where [condition]

Is there any easier way to do this whithout using while or modifing any table?

Answer 1

I found this question when searching for a solution a similar case. The only difference was that I wanted to fill TableB with an row for each row in TableA (no where-clause).

I found a solution to the third option you suggeted (using output from insert), however, since you are using INSERT with data from a SELECT, you cannot use the OUTPUT clause. This pointed me in the right direction:

DECLARE @MyTableVar table(tableBId int, tableAId int)

MERGE INTO TableB
using TableA AS AP
on 1=0
WHEN NOT MATCHED THEN
   Insert(some_other_data) 
           Values('some_other_data')
           Output inserted.ID, AP.ID INTO @MyTableVar;

update TableA set new_FK_column_on_B = (select tableBId from @MyTableVar where tableAId = TableA.ID)

Be aware that executing this a seconds time will create new entries in TableB. If you only want to create new rows in TableB, where there is no foreign key set in TableA, you can use this script:

DECLARE @MyTableVar TABLE(tableBId int, tableAId int)

MERGE INTO TableB AS B
USING TableA AS AP
ON A.new_FK_column_on_B = B.id
WHEN NOT MATCHED THEN
   INSERT(some_data) 
           VALUES(AP.some_data)
           OUTPUT inserted.ID, AP.ID INTO @MyTableVar;

UPDATE TableA SET new_FK_column_on_B = (
   SELECT tableBId
   FROM @MyTableVar
   WHERE tableAId = TableA.ID )
WHERE TableA.new_FK_column_on_B IS NULL;