title: Update record's foreign key field based on a newly inserted record's primary key
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Table A:</p>
  
  <pre><code>|Id|ForeignKey|
  |1 |NULL      |
  |2 |NULL      |
  |3 |NULL      |
  |4 |NULL      |
  |5 |NULL      |
  </code></pre>
  
  <p>As a pseudo code I thought of something like this sql:</p>
  
  <pre><code>update TableA 
  set ForeignKey = (INSERT INTO TableB VALUES (value1) select SCOPE_IDENTITY())
  </code></pre>
  
  <p>Any idea?</p>