title: Is it possible to update parentID as part of a move operation without using temporary columns?
body: <p>The problem I have is that I have an old database where there typically are a lot of tables with internal parent/child relationship. These tables are getting migrated to a newer data structure, but we want to keep the old relationship. Today I do this as follow: </p>
  
  <pre><code>Old table is tbl_contract. PK: contract_pk, FK: contract_parent_id, contract_name
  New table is contract. PK: ID, FK contract_parent_id, name
  
  ALTER TABLE contract ADD contract_pk INT NULL
  INSERT INTO contract ( [name], contract_pk )
      SELECT contract_name, contract_pk FROM dbo.tbl_contract
  UPDATE contract SET contract_parent_id = 
      (SELECT c.id FROM contract as c WHERE c.contract_pk = 
           (SELECT contract_parent FROM tbl_contract 
            WHERE contract_pk = contract.contract_pk)
       )
  ALTER TABLE contract DROP COLUMN contract_pk
  </code></pre>
  
  <p>Is there any easier way of doing this?</p>