如何从竖线分隔CSV向表更新/插入数据？SQL Server 14版本受限

我来帮你搞定这个需求——把临时表中用|分隔的部门拆分成多条记录，然后对Emp表执行更新/插入操作。既然你说SQL Server 14没法用STRING_SPLIT，我给你几个实用的替代方案，还有一个可能的小坑提醒：

先确认临时表结构（方便测试） #

假设你的临时表是#TempEmp，先给出测试用的创建和插入语句：

CREATE TABLE #TempEmp (
    ID VARCHAR(20),
    DEPARTMENT VARCHAR(100),
    DESIGNATION VARCHAR(50),
    LOCATION VARCHAR(50),
    REGION VARCHAR(50),
    STATE VARCHAR(50)
);

INSERT INTO #TempEmp VALUES ('Test19768', 'Barclays|Citi Bank|Ireland', 'developer', NULL, NULL, NULL);

方法1：递归CTE拆分（最容易理解） #

递归CTE会一步步把分隔字符串拆成单个部门，适合简单场景：

WITH SplitDepartments AS (
    -- 第一步：提取第一个部门，同时保留剩余未拆分的字符串
    SELECT 
        ID,
        DESIGNATION,
        LOCATION,
        REGION,
        STATE,
        LEFT(DEPARTMENT, CHARINDEX('|', DEPARTMENT + '|') - 1) AS SplitDept,
        STUFF(DEPARTMENT, 1, CHARINDEX('|', DEPARTMENT + '|'), '') AS RemainingDept
    FROM #TempEmp
    WHERE DEPARTMENT IS NOT NULL AND DEPARTMENT <> ''

    UNION ALL

    -- 递归：继续拆分剩余字符串，直到拆完
    SELECT 
        ID,
        DESIGNATION,
        LOCATION,
        REGION,
        STATE,
        LEFT(RemainingDept, CHARINDEX('|', RemainingDept + '|') - 1) AS SplitDept,
        STUFF(RemainingDept, 1, CHARINDEX('|', RemainingDept + '|'), '') AS RemainingDept
    FROM SplitDepartments
    WHERE RemainingDept IS NOT NULL AND RemainingDept <> ''
)
-- 用MERGE做UPSERT（匹配则更新，不匹配则插入）
MERGE INTO Emp AS Target
USING (
    SELECT 
        ID,
        SplitDept AS DEPARTMENT,
        DESIGNATION,
        LOCATION,
        REGION,
        STATE
    FROM SplitDepartments
) AS Source
ON Target.ID = Source.ID AND Target.DEPARTMENT = Source.DEPARTMENT
WHEN MATCHED THEN
    UPDATE SET 
        DESIGNATION = Source.DESIGNATION,
        LOCATION = Source.LOCATION,
        REGION = Source.REGION,
        STATE = Source.STATE
WHEN NOT MATCHED THEN
    INSERT (ID, DEPARTMENT, DESIGNATION, LOCATION, REGION, STATE)
    VALUES (Source.ID, Source.DEPARTMENT, Source.DESIGNATION, Source.LOCATION, Source.REGION, Source.STATE);

方法2：数字表拆分（性能更优） #

如果要处理大量数据，用数字表拆分的性能会比递归CTE好。先临时生成一个数字表，再用它定位分隔符位置：

-- 临时生成一个包含1到1000的数字表（足够拆分大部分字符串）
SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY s1.object_id)
INTO #Numbers
FROM sys.all_objects s1 CROSS JOIN sys.all_objects s2;

-- 拆分字符串
WITH SplitDepartments AS (
    SELECT 
        te.ID,
        te.DESIGNATION,
        te.LOCATION,
        te.REGION,
        te.STATE,
        SplitDept = SUBSTRING(te.DEPARTMENT, n, CHARINDEX('|', te.DEPARTMENT + '|', n) - n)
    FROM #TempEmp te
    JOIN #Numbers n 
        ON n.n <= LEN(te.DEPARTMENT) + 1
        AND SUBSTRING('|' + te.DEPARTMENT, n, 1) = '|'
)
-- 同样执行MERGE操作
MERGE INTO Emp AS Target
USING (
    SELECT 
        ID,
        SplitDept AS DEPARTMENT,
        DESIGNATION,
        LOCATION,
        REGION,
        STATE
    FROM SplitDepartments
    WHERE SplitDept <> '' -- 过滤空值
) AS Source
ON Target.ID = Source.ID AND Target.DEPARTMENT = Source.DEPARTMENT
WHEN MATCHED THEN
    UPDATE SET 
        DESIGNATION = Source.DESIGNATION,
        LOCATION = Source.LOCATION,
        REGION = Source.REGION,
        STATE = Source.STATE
WHEN NOT MATCHED THEN
    INSERT (ID, DEPARTMENT, DESIGNATION, LOCATION, REGION, STATE)
    VALUES (Source.ID, Source.DEPARTMENT, Source.DESIGNATION, Source.LOCATION, Source.REGION, Source.STATE);

-- 用完临时数字表记得删掉
DROP TABLE #Numbers;

方法3：XML拆分（代码最简洁） #

把分隔字符串转换成XML格式，再提取每个节点值，代码很紧凑：

WITH SplitDepartments AS (
    SELECT 
        te.ID,
        te.DESIGNATION,
        te.LOCATION,
        te.REGION,
        te.STATE,
        SplitDept = x.value('.', 'VARCHAR(100)')
    FROM #TempEmp te
    -- 把|替换成XML节点标签，转成XML类型
    CROSS APPLY (
        SELECT CAST('<d>' + REPLACE(te.DEPARTMENT, '|', '</d><d>') + '</d>' AS XML) AS DeptXML
    ) AS t
    -- 拆分每个XML节点
    CROSS APPLY t.DeptXML.nodes('/d') AS x(x)
)
-- MERGE操作同上
MERGE INTO Emp AS Target
USING (
    SELECT 
        ID,
        SplitDept AS DEPARTMENT,
        DESIGNATION,
        LOCATION,
        REGION,
        STATE
    FROM SplitDepartments
    WHERE SplitDept <> ''
) AS Source
ON Target.ID = Source.ID AND Target.DEPARTMENT = Source.DEPARTMENT
WHEN MATCHED THEN
    UPDATE SET 
        DESIGNATION = Source.DESIGNATION,
        LOCATION = Source.LOCATION,
        REGION = Source.REGION,
        STATE = Source.STATE
WHEN NOT MATCHED THEN
    INSERT (ID, DEPARTMENT, DESIGNATION, LOCATION, REGION, STATE)
    VALUES (Source.ID, Source.DEPARTMENT, Source.DESIGNATION, Source.LOCATION, Source.REGION, Source.STATE);

额外提醒：可能你其实能用STRING_SPLIT？ #

STRING_SPLIT是SQL Server 2016（版本13）及以后支持的，SQL Server 14（2017）本来应该支持。如果用不了，大概率是数据库兼容性级别设置太低。你可以检查一下：

SELECT name, compatibility_level FROM sys.databases WHERE name = '你的数据库名';

如果兼容性级别低于130，改成130或更高就能用STRING_SPLIT了：

ALTER DATABASE 你的数据库名 SET COMPATIBILITY_LEVEL = 130;

用STRING_SPLIT的话代码会更简单：

MERGE INTO Emp AS Target
USING (
    SELECT 
        te.ID,
        s.value AS DEPARTMENT,
        te.DESIGNATION,
        te.LOCATION,
        te.REGION,
        te.STATE
    FROM #TempEmp te
    CROSS APPLY STRING_SPLIT(te.DEPARTMENT, '|') s
    WHERE s.value <> ''
) AS Source
ON Target.ID = Source.ID AND Target.DEPARTMENT = Source.DEPARTMENT
WHEN MATCHED THEN
    UPDATE SET 
        DESIGNATION = Source.DESIGNATION,
        LOCATION = Source.LOCATION,
        REGION = Source.REGION,
        STATE = Source.STATE
WHEN NOT MATCHED THEN
    INSERT (ID, DEPARTMENT, DESIGNATION, LOCATION, REGION, STATE)
    VALUES (Source.ID, Source.DEPARTMENT, Source.DESIGNATION, Source.LOCATION, Source.REGION, Source.STATE);

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