SQL Server：拆分逗号分隔值实现多行Insert（无需临时表/少改旧代码）

问题背景 #

我尝试执行以下SQL操作，先清理旧的供应商关联记录，再插入新的关联数据：

DELETE FROM product 
WHERE product_id = 'some_id' 
  AND supplier_id IN ('some_id1', 'some_id2');

INSERT INTO product (product_id, is_active, supplier_id)
SELECT 'some_id' AS product_id, 
       1 AS is_active, 
       newidst.new_ids AS supplier_id
FROM (SELECT ('new_id1,new_id2') AS new_ids) newidst;

预期效果是删除0行（无匹配记录），并插入2行数据：

some_id, 1, new_id1
some_id, 1, new_id2

但实际只插入了1行——因为系统把'new_id1,new_id2'当成了单个字符串值，没有拆分出两个独立的供应商ID。

我不想创建临时表，而且当ID列表很长时，用UNION ALL逐个拼接会非常繁琐。希望能尽量少修改原有查询，找到可行的解决办法。

解决方案（分数据库适配） #

下面的方案都只需要修改INSERT语句的查询部分，原有的DELETE语句完全不用动，完美符合“少改动”的需求：

1. SQL Server 环境

直接用内置的STRING_SPLIT函数拆分字符串，这是最简单的方式：

INSERT INTO product (product_id, is_active, supplier_id)
SELECT 'some_id' AS product_id, 
       1 AS is_active, 
       value AS supplier_id
FROM STRING_SPLIT('new_id1,new_id2', ',');

STRING_SPLIT会自动把逗号分隔的字符串拆分成多行，每一行对应一个供应商ID，直接插入即可。

2. MySQL 8.0+ 环境

用递归CTE（公共表表达式）来拆分长字符串，不需要临时表：

INSERT INTO product (product_id, is_active, supplier_id)
WITH RECURSIVE split_ids AS (
  -- 初始行：取第一个ID和剩余字符串
  SELECT 1 AS pos, 
         SUBSTRING_INDEX('new_id1,new_id2', ',', 1) AS supplier_id,
         SUBSTRING('new_id1,new_id2', LENGTH(SUBSTRING_INDEX('new_id1,new_id2', ',', 1)) + 2) AS remaining
  UNION ALL
  -- 递归拆分剩余字符串，直到为空
  SELECT pos + 1,
         SUBSTRING_INDEX(remaining, ',', 1),
         SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
  FROM split_ids
  WHERE remaining != ''
)
SELECT 'some_id' AS product_id, 1 AS is_active, supplier_id
FROM split_ids;

不管ID列表有多长，这个递归逻辑都能自动拆分，不用手动写一堆UNION ALL。

3. PostgreSQL 环境

用string_to_array把字符串转成数组，再用unnest把数组拆成多行：

INSERT INTO product (product_id, is_active, supplier_id)
SELECT 'some_id' AS product_id, 
       1 AS is_active, 
       unnest(string_to_array('new_id1,new_id2', ',')) AS supplier_id;

这个组合函数是PostgreSQL处理字符串拆分的常用方式，简洁高效。

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