[SOLVED] Integer to guid primary key database migration

Integer to guid primary key database migration

I'm trying to migrate from int based primary keys to guid based system, and I'm having trouble with migrating self referenced table

Entity
---------
ID
ParentID
AnotherParentID

When querying the table I'm creating new guid ID for each row, but how can I set that same guid value for rows that have that ID as foreign key on ParentID or AnotherParentID fields?

SELECT 
    newid() as ID,
    (SELECT e.ID ...?) as ParentID,
    (SELECT e.ID ...?) as AnotherParentID,
FROM Entity e

Solution

@MRsa answer gives multiple rows because of joining on ParentID or AnotherParentID (since multiple rows can have same ParentID/AnotherParentID).

This is my current implementation that works, I'm not sure if there is any better way to handle this

CREATE TABLE #tmp (
    Id uniqueidentifier null,
    OldId integer null
)

INSERT INTO #tmp (Id, OldId)
SELECT NEWID(), Id FROM OldTable

INSERT INTO NewTable
SELECT
    (SELECT Id FROM #tmp WHERE OldId = i.Id) as ID,
    (SELECT Id FROM #tmp WHERE OldId = i.ParentId) as ParentID,
    (SELECT Id FROM #tmp WHERE OldId = i.AnotherParentID) as AnotherParentID,
FROM OldTable i