USE MapDiffBotv3;
set xact_abort on;
BEGIN TRANSACTION;
CREATE TABLE Images (ID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY, [Data] varbinary(max) NOT NULL, TempInstallalationId BIGINT ,
TempCheckRunId BIGINT, TempFileId int);
ALTER TABLE MapDiffs ADD BeforeImageId BIGINT FOREIGN KEY REFERENCES Images(Id);
ALTER TABLE MapDiffs ADD AfterImageId BIGINT FOREIGN KEY REFERENCES Images(Id);
ALTER TABLE MapDiffs ADD DifferenceImageId BIGINT FOREIGN KEY REFERENCES Images(Id)
INSERT INTO Images ([Data], TempInstallalationId, TempCheckRunId, TempFileId)
SELECT BeforeImage, InstallationRepositoryId, CheckRunId, FileId FROM MapDiffs AS MD WHERE MD.BeforeImage IS NOT NULL;
UPDATE MD SET BeforeImageId = I.Id FROM MapDiffs MD JOIN Images I
ON MD.InstallationRepositoryId = I.TempInstallalationId AND MD.CheckRunId = I.TempCheckRunId AND
MD.FileId = I.TempFileId;
ALTER TABLE Images DROP COLUMN TempInstallalationId, TempCheckRunId, TempFileId;
ALTER TABLE Images ADD TempInstallalationId BIGINT, TempCheckRunId BIGINT, TempFileId int;
INSERT INTO Images ([Data], TempInstallalationId, TempCheckRunId, TempFileId)
SELECT AfterImage, InstallationRepositoryId, CheckRunId, FileId FROM MapDiffs AS MD WHERE MD.AfterImage IS NOT NULL;
UPDATE MD SET AfterImageId = I.Id FROM MapDiffs MD JOIN Images I
ON MD.InstallationRepositoryId = I.TempInstallalationId AND MD.CheckRunId = I.TempCheckRunId AND
MD.FileId = I.TempFileId;
ALTER TABLE Images DROP COLUMN TempInstallalationId, TempCheckRunId, TempFileId;
ALTER TABLE MapDiffs DROP COLUMN BeforeImage, AfterImage;
COMMIT;
Closes #73
Here is SQL Server migrations for the change