Merging SQL Server Tables

In one of my existing databases I had to switch from integer key to GUID. Annoying aspect of it is handling referential integrity issues and, as soon as there are two tables, you can bet that there will be issues.

Process was half manual and simple one: just create new field (e.g. Guid) and give it default of NEWID. Database itself will do the rest for that field's value.

In order to sort out references, you can go in similar fashion. For every foreign key field just create one with GUID (E.g. ItemId gets it's friend ItemGuid). After that just synchronize fields with simple SQL:

UPDATE Codes
SET ItemGuid = (
    SELECT Guid
    FROM Items
    WHERE Items.Id = Codes.ItemId
)

In this case, we had referential link between Codes.ItemId and Items.Id. Our goal was to switch that to Codes.ItemGuid and Items.Guid. Where statement ensured just that.