如何将SQL Server中单表竖线分隔行拆分插入新表多列？

针对你固定26列、竖线分隔且首尾带竖线的场景，推荐两种不依赖固定字符位置的方法，替代容易出错的SUBSTRING写法：

方法1：利用STRING_SPLIT（SQL Server 2022及以上版本） #

SQL Server 2022新增了STRING_SPLIT的ordinal参数，能直接获取每个拆分项的顺序位置，配合PIVOT可以快速将行转成对应列：

INSERT INTO dbo.tblNew (Column1, Column2, ..., Column26)
SELECT [1], [2], [3], ..., [26]
FROM (
    SELECT 
        TRIM(value) AS split_value, -- 自动清理每个值前后的空格
        ordinal
    FROM #tblTemp
    -- 先去掉首尾的竖线，再按竖线拆分字符串
    CROSS APPLY STRING_SPLIT(TRIM(BOTH '|' FROM tempData), '|', 1)
) AS src
PIVOT (
    MAX(split_value) FOR ordinal IN ([1], [2], [3], ..., [26])
) AS pvt;

方法2：XML拆分法（兼容SQL Server 2012及以上版本） #

如果你的SQL Server版本低于2022，用XML转换的方式也能按顺序拆分字符串：

INSERT INTO dbo.tblNew (Column1, Column2, ..., Column26)
SELECT
    -- 按顺序提取第1到26个节点的内容，TRIM清理空格
    TRIM(xmlData.value('/root[1]/item[1]', 'VARCHAR(100)')) AS Column1,
    TRIM(xmlData.value('/root[1]/item[2]', 'VARCHAR(100)')) AS Column2,
    ...
    TRIM(xmlData.value('/root[1]/item[26]', 'VARCHAR(100)')) AS Column26
FROM (
    SELECT 
        -- 将原字符串转换为XML结构：把竖线替换成XML节点标签
        CAST('<root><item>' + REPLACE(TRIM(BOTH '|' FROM tempData), '|', '</item><item>') + '</item></root>' AS XML) AS xmlData
    FROM #tblTemp
) AS src;

两种方法的优势 #

相比你之前用SUBSTRING的写法，这两种方法都不依赖固定的字符位置，不管每个分隔项的长度如何变化，只要列数保持26不变，就能准确将每个值对应到新表的对应列中，避免了位置偏移导致的错误。

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