SQL Server中如何将两个逗号分隔字符串转为对应行插入表中

你的问题出在拆分后没有将两个变量的对应位置元素关联，而是分别插入两个独立的行集，导致数据错位。核心思路是拆分时保留每个元素的位置序号，再通过序号将两个拆分结果关联，从而得到一一对应的item1和item2组合。

SQL Server 2022及以上版本 #

从SQL Server 2022开始，STRING_SPLIT支持第三个参数启用ordinal列（表示元素在原字符串中的位置），可以直接用这个序号关联两个拆分结果：

DECLARE @item1 NVARCHAR(100) = 'abc,def';
DECLARE @item2 NVARCHAR(100) = 'ghi,jkl';

-- 替换为你的实际表名
INSERT INTO YourTable (item1, item2)
SELECT s1.value, s2.value
FROM STRING_SPLIT(@item1, ',', 1) s1
JOIN STRING_SPLIT(@item2, ',', 1) s2 
  ON s1.ordinal = s2.ordinal;

SQL Server 2019及以下版本 #

低版本STRING_SPLIT不支持ordinal，需要自定义带序号的拆分函数，或者用XML解析生成序号关联：

方法1：自定义拆分函数 #

先创建带序号的拆分函数：

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

然后使用函数关联插入：

DECLARE @item1 NVARCHAR(100) = 'abc,def';
DECLARE @item2 NVARCHAR(100) = 'ghi,jkl';

INSERT INTO YourTable (item1, item2)
SELECT s1.value, s2.value
FROM dbo.SplitStringWithOrdinal(@item1, ',') s1
JOIN dbo.SplitStringWithOrdinal(@item2, ',') s2 
  ON s1.ordinal = s2.ordinal;

方法2：无自定义函数的XML拆分法 #

如果无法创建函数，可以用XML解析的方式生成序号并关联：

DECLARE @item1 NVARCHAR(100) = 'abc,def';
DECLARE @item2 NVARCHAR(100) = 'ghi,jkl';

INSERT INTO YourTable (item1, item2)
SELECT 
    t1.item AS item1,
    t2.item AS item2
FROM (
    SELECT 
        Split.a.value('.', 'NVARCHAR(100)') AS item,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (SELECT CAST('<X>'+REPLACE(@item1, ',', '</X><X>')+'</X>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes('X') AS Split(a)
) t1
JOIN (
    SELECT 
        Split.a.value('.', 'NVARCHAR(100)') AS item,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (SELECT CAST('<X>'+REPLACE(@item2, ',', '</X><X>')+'</X>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes('X') AS Split(a)
) t2 ON t1.ordinal = t2.ordinal;

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