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

Question

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

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

For example, for each row in the following table, I want to update the Zero Foreign Key field based on the creation of 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 the cursor to cycle through table A and create 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 

Answer 2

The set-based way to do the update correlated with the liner in SQL Server 2008, and then to use Merge ... CONCLUSION statement.

 -- This table variable will hold the correlation between the two tables declare @NewLinks table (dst_id int, src_id int) merge TableB dst using TableA src on 1 = 0 --inserts all from src into dst, add WHERE-like limit here when not matched --should never match, since this is an INSERT-like query insert (<dst_column_list>) values (<src_column_list>) output INSERTED.<dst_surrogate_key>, src.<src_surrogate_key> into @NewLinks update src set src.ForeignKey = nl.dst_id from TableA src join @NewLinks nl on nl.src_id = src.Id 

Since the INSERT statement (even in the form of INSERT ... SELECT) does not have a FROM clause, from_table_name cannot refer in its OUTPUT clause to fix the full correlation. Because MERGE always considers its USING clause to be a FROM clause, it can be used to simulate the INSERT ... SELECT construct, providing access to the source and destination tables in OUTPUT.

Using MERGE ... CONCLUSION to create UPDATE correlated with INSERT replaces inefficient use of RBAR solutions using cursors or loops with the contents of one temporary table or table-valued variable.