EF与T-SQL：重复数据下将列数据迁移至新表并映射外键

背景 #

原Product实体直接存储JiraProjectKey字段：

public class Product
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string JiraProjectKey { get; set; }
    // 其他字段
}

为扩展Jira集成配置，新增JiraIntegrationSettings实体：

public class JiraIntegrationSettings
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string JiraProjectKey { get; set; }
    // 其他新增配置字段
}

修改后的Product实体通过外键关联新表：

public class Product
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? JiraIntegrationSettingsId { get; set; }

    public JiraIntegrationSettings JiraIntegrationSettings { get; set; }
    // 其他字段
}

核心需求：即使原Product存在重复的JiraProjectKey，迁移后每个Product必须对应独立的JiraIntegrationSettings记录（而非共享同一条记录）。

迁移实现方案 #

1. 数据迁移T-SQL脚本 #

通过临时表记录Product与新生成的JiraIntegrationSettings的对应关系，确保每个Product生成独立配置记录：

-- 创建临时表，存储ProductID和对应的新配置ID
CREATE TABLE #TempProductSettings (ProductId INT, SettingsId INT)

-- 为每个Product插入独立的JiraIntegrationSettings记录，同时记录对应关系
INSERT INTO [JiraIntegrationSettings] ([JiraProjectKey])
OUTPUT inserted.Id, p.Id INTO #TempProductSettings(SettingsId, ProductId)
SELECT [JiraProjectKey]
FROM [Products] p

-- 更新Product表的外键字段，关联对应的配置记录
UPDATE p
SET p.JiraIntegrationSettingsId = t.SettingsId
FROM [Products] p
JOIN #TempProductSettings t ON p.Id = t.ProductId

-- 清理临时表
DROP TABLE #TempProductSettings

2. 迁移验证脚本 #

执行以下查询验证每个Product都已关联独立的配置记录：

-- 检查是否存在未关联或关联多条配置的Product
SELECT p.Id, COUNT(j.Id) AS SettingsCount
FROM [Products] p
LEFT JOIN [JiraIntegrationSettings] j ON p.JiraIntegrationSettingsId = j.Id
GROUP BY p.Id
HAVING COUNT(j.Id) != 1

方案说明 #

• 区别于共享记录的SELECT DISTINCT方案，本脚本直接遍历所有Product插入配置记录，保证重复JiraProjectKey对应多条独立的JiraIntegrationSettings。
• 利用OUTPUT子句和临时表，避免多次扫描表，提升迁移效率。

内容的提问来源于stack exchange，提问作者Ogglas