Integer to guid primary key database migration

Question

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

You won't be able to do this in one pass, you'll have to retain the old and new keys and update accordingly. — Thom A, Oct 17 '19 at 07:40
Like store new and old key in temp table, and then just query new key from that table where OldID = RequestedID? — zhuber, Oct 17 '19 at 07:42

MRsa · Answer 1 · 2019-10-17T07:47:57.543

IF paretnIDs are Foreign keys:

Select NEWID(), ID, EP.ParentIDGUID, E.ParentID, EP2.AnotherParentID, EP2.AnotherParentIDGUID from dbo.Entity E  
        INNER JOIN 
        (Select MAX(NEWID()) as ParentIDGUID, ParentID FROM dbo.Entity GROUP BY ParentID) EP ON EP.ParentID = E.ParentID
        INNER JOIN 
        (Select MAX(NEWID()) as AnotherParentIDGUID, AnotherParentID FROM dbo.Entity GROUP BY AnotherParentID) EP2 ON EP2.AnotherParentID = E.AnotherParentID

or if they are foreign key on same table:

Select NEWID() as guidID, ID
INTO #tp 
 from dbo.Entity E


Select EP.ID, EP.guidID, E.ParentID, EP2.guidID, E.AnotherParentID, EP3.guidID from dbo.Entity E  
    INNER JOIN 
    #tp EP ON EP.ID = E.ID
    INNER JOIN 
    #tp EP2 ON EP2.ID = E.ParentID
    INNER JOIN 
    #tp EP3 ON EP3.ID = E.AnotherParentID


DROP TABLE #tp

score 1 · Accepted Answer · answered Oct 17 '19 at 08:23

@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

Integer to guid primary key database migration

2 Answers2