如何将单表竖线分隔行插入至不同结构的SQL目标表？

核心思路 #

1. 提取目标表标识：从源表每行column1的前两位字符，确定对应的目标表（aa/bb/cc/dd）
2. 安全拆分字符串：利用示例中分隔符的特征（|前后带空格）拆分字段，规避字段内部可能存在的竖线干扰
3. 分表映射插入：针对不同目标表的结构，分别处理字段映射并执行集合式插入，替代低效的循环操作

优化后的存储过程 #

CREATE PROCEDURE dbo.IngestRows
AS
BEGIN
    SET NOCOUNT ON;

    -- 插入到aa表：取拆分后的第2、3个字段
    INSERT INTO dbo.aa (i, j)
    SELECT 
        LTRIM(RTRIM(s.value)) AS i,
        LTRIM(RTRIM(LEAD(s.value, 1) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS j
    FROM dbo.source_table src
    CROSS APPLY STRING_SPLIT(src.column1, '|', 1) s
    WHERE src.column1 LIKE 'aa%'
        AND s.ordinal = 2;

    -- 插入到bb表：取拆分后的第2、3、4个字段
    INSERT INTO dbo.bb (k, l, m)
    SELECT 
        LTRIM(RTRIM(s.value)) AS k,
        LTRIM(RTRIM(LEAD(s.value, 1) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS l,
        LTRIM(RTRIM(LEAD(s.value, 2) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS m
    FROM dbo.source_table src
    CROSS APPLY STRING_SPLIT(src.column1, '|', 1) s
    WHERE src.column1 LIKE 'bb%'
        AND s.ordinal = 2;

    -- 插入到cc表：取拆分后的第2个字段
    INSERT INTO dbo.cc (p)
    SELECT 
        LTRIM(RTRIM(s.value)) AS p
    FROM dbo.source_table src
    CROSS APPLY STRING_SPLIT(src.column1, '|', 1) s
    WHERE src.column1 LIKE 'cc%'
        AND s.ordinal = 2;

    -- 插入到dd表：取拆分后的第2、3、4、5个字段
    INSERT INTO dbo.dd (r, s, t, u)
    SELECT 
        LTRIM(RTRIM(s.value)) AS r,
        LTRIM(RTRIM(LEAD(s.value, 1) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS s,
        LTRIM(RTRIM(LEAD(s.value, 2) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS t,
        LTRIM(RTRIM(LEAD(s.value, 3) OVER (PARTITION BY src.column1 ORDER BY s.ordinal))) AS u
    FROM dbo.source_table src
    CROSS APPLY STRING_SPLIT(src.column1, '|', 1) s
    WHERE src.column1 LIKE 'dd%'
        AND s.ordinal = 2;
END
GO

关键说明 #

1. 字符串拆分逻辑：使用SQL Server 2022+支持的STRING_SPLIT序号参数，确保拆分后的字段顺序稳定；通过LTRIM/RTRIM清除字段前后的冗余空格，同时保留字段内部的竖线/逗号内容
2. 字段映射方式：利用LEAD窗口函数获取同一行拆分后的后续字段，精准匹配不同目标表的列数需求
3. 性能优化：采用集合式操作替代原存储过程的WHILE循环，大幅提升数据处理效率
4. 兼容旧版本SQL Server：若你的版本不支持STRING_SPLIT序号参数，可使用以下自定义拆分函数替代：

CREATE FUNCTION dbo.SplitStringWithOrdinal
(
    @input VARCHAR(MAX),
    @delimiter VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM STRING_SPLIT(@input, @delimiter)
)
GO

替换时只需将存储过程中的STRING_SPLIT(src.column1, '|', 1)改为dbo.SplitStringWithOrdinal(src.column1, '|')即可

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