如何用T-SQL将表字段转换为新表？EF Code First数据补全咨询

我来帮你逐个解决这两个问题：

问题1：使用T-SQL将某张表的字段转换为另一张表 #

这里分两种常见场景来处理，你可以根据实际需求选择：

场景1：提取字段的唯一值生成维度表（比如把Sale表的TerminalCode转到Terminal表） #

这是最常见的情况，适合把重复的字段值抽成独立实体表：

1. 先创建目标表（如果还没建）：

CREATE TABLE Terminal (
    TerminalId INT IDENTITY(1,1) PRIMARY KEY,
    TerminalCode VARCHAR(50) UNIQUE NOT NULL
    -- 这里可以添加Terminal的其他属性字段，比如名称、地址等
)

2. 将原表中唯一的TerminalCode插入新表：

INSERT INTO Terminal (TerminalCode)
SELECT DISTINCT TerminalCode
FROM Sale
WHERE TerminalCode IS NOT NULL AND TerminalCode <> '' -- 过滤空值和空字符串

3. （可选）给原表添加外键关联新表，建立关系：

-- 先添加外键列
ALTER TABLE Sale ADD TerminalId INT
-- 更新外键值，关联对应的Terminal记录
UPDATE s
SET s.TerminalId = t.TerminalId
FROM Sale s
JOIN Terminal t ON s.TerminalCode = t.TerminalCode
-- 添加外键约束，保证数据一致性
ALTER TABLE Sale
ADD CONSTRAINT FK_Sale_Terminal FOREIGN KEY (TerminalId) REFERENCES Terminal(TerminalId)

场景2：拆分单个字段的多值生成新表记录 #

如果原字段是用分隔符（比如逗号）存储多个编码，需要拆分后插入新表：

INSERT INTO Terminal (TerminalCode)
SELECT DISTINCT value
FROM Sale
CROSS APPLY STRING_SPLIT(Sale.TerminalCodes, ',') -- 假设原字段名为TerminalCodes，逗号分隔
WHERE value IS NOT NULL AND value <> '' -- 过滤无效值

注意：STRING_SPLIT函数从SQL Server 2016及以上版本支持，低版本需要自定义字符串拆分函数。

问题2：EF Code First批量创建Terminal条目关联原Sale实体 #

既然你已经完成了实体定义、关联配置和迁移，接下来可以用两种方式批量处理，根据数据量选择：

方式1：用EF Core代码逻辑处理（适合中小数据量） #

假设你的实体类结构大致如下：

public class Sale
{
    public int Id { get; set; }
    public string TerminalCode { get; set; } // 保留的旧属性
    public Terminal Terminal { get; set; } // 新的导航属性
    public int? TerminalId { get; set; } // 外键字段
}

public class Terminal
{
    public int Id { get; set; }
    public string Code { get; set; } // 对应当初的TerminalCode
    // 其他Terminal属性，比如CreateTime、Description等
    public ICollection<Sale> Sales { get; set; } = new List<Sale>();
}

实现代码：

using (var context = new YourDbContext()) // 替换成你的DbContext类
{
    // 1. 获取所有需要创建的唯一TerminalCode，去重并过滤空值
    var uniqueTerminalCodes = context.Sales
        .Where(s => !string.IsNullOrWhiteSpace(s.TerminalCode))
        .Select(s => s.TerminalCode.Trim())
        .Distinct()
        .ToList();

    // 2. 找出数据库中已存在的Terminal编码，避免重复创建
    var existingCodes = context.Terminals
        .Select(t => t.Code)
        .ToList();

    // 3. 筛选出需要新增的Terminal实体
    var terminalsToAdd = uniqueTerminalCodes
        .Where(code => !existingCodes.Contains(code))
        .Select(code => new Terminal { Code = code })
        .ToList();

    // 4. 批量添加到数据库
    context.Terminals.AddRange(terminalsToAdd);
    await context.SaveChangesAsync();

    // 5. 关联Sale和对应的Terminal
    // 如果数据量很大，建议分批次处理，避免内存压力
    var salesToUpdate = context.Sales
        .Where(s => !string.IsNullOrWhiteSpace(s.TerminalCode) && s.TerminalId == null)
        .ToList();

    foreach (var sale in salesToUpdate)
    {
        var terminal = context.Terminals.FirstOrDefault(t => t.Code == sale.TerminalCode.Trim());
        if (terminal != null)
        {
            sale.TerminalId = terminal.Id;
        }
    }

    await context.SaveChangesAsync();
}

方式2：执行原生SQL（适合大数据量，性能更高） #

如果你的Sale表数据量很大，用EF循环处理效率低，直接执行SQL更高效：

using (var context = new YourDbContext())
{
    // 第一步：批量插入唯一的TerminalCode到Terminal表，自动跳过已存在的记录
    await context.Database.ExecuteSqlRawAsync(@"
        INSERT INTO Terminals (Code)
        SELECT DISTINCT LTRIM(RTRIM(TerminalCode))
        FROM Sales
        WHERE TerminalCode IS NOT NULL AND TerminalCode <> ''
        AND NOT EXISTS (SELECT 1 FROM Terminals WHERE Code = LTRIM(RTRIM(Sales.TerminalCode)))
    ");

    // 第二步：批量更新Sale的TerminalId，关联对应的Terminal
    await context.Database.ExecuteSqlRawAsync(@"
        UPDATE s
        SET s.TerminalId = t.Id
        FROM Sales s
        JOIN Terminals t ON LTRIM(RTRIM(s.TerminalCode)) = t.Code
        WHERE s.TerminalId IS NULL
    ");
}

后续清理（可选） #

当所有关联都完成后，如果你不再需要旧的TerminalCode字段，可以创建一个迁移删除它：

public partial class RemoveTerminalCodeFromSale : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "TerminalCode",
            table: "Sales");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "TerminalCode",
            table: "Sales",
            type: "nvarchar(max)",
            nullable: true);
    }
}

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