Update record's foreign key field based on a newly inserted record's primary key

Question

For each record in table A I want to update the foreign key value of one of the fields based on new inserted record's scope_identity in table B.

I need to create a new record in table B for each record in table A in order to receive a foreign key(scope_identity) value.

For example, for each row in the following table I want to update the null Foreign Key field based on creating a new row/foreign key in Table B.

Table A:

|Id|ForeignKey|
|1 |NULL      |
|2 |NULL      |
|3 |NULL      |
|4 |NULL      |
|5 |NULL      |

As a pseudo code I thought of something like this sql:

update TableA 
set ForeignKey = (INSERT INTO TableB VALUES (value1) select SCOPE_IDENTITY())

Any idea?

Answer 1

You can use a cursor to loop through TableA and create the records:

DECLARE @Id int
DECLARE @ForeignKey int

DECLARE C CURSOR FOR SELECT Id FROM TableA

OPEN C

FETCH NEXT FROM C INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO TableB VALUES (value1)
    SET @ForeignKey = SCOPE_IDENTITY()

    UPDATE TableA
    SET ForeignKey = @ForeignKey
    WHERE Id = @Id

    FETCH NEXT FROM C INTO @Id
END

CLOSE C
DEALLOCATE C