Is it possible to update parentID as part of a move opperation without using temporary columns?

Question

The problem I have is that I have an old database where there typicaly 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:

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

Is there any easier way of doing this?

Answer 1

Assuming ID is a identity column and has the same type as contract_pk, you can use SET IDENTITY_INSERT ON to allow you to override the auto-generated identity column with the old values:

SET IDENTITY_INSERT contract ON

INSERT INTO contract ( ID, contract_parent_id, [name] )
SELECT contract_pk, contract_parent_id, contract_name FROM dbo.tbl_contract

SET IDENTITY_INSERT contract OFF

Assuming you have an FK constraint on contract_parent_id, you'll likely need to disable that first as well, then re-enable it after doing the insert.