SQL中将字符串变量存储的CSV转换为临时表的方案咨询

我明白你的痛点：CSV表头不固定时，硬编码列名完全行不通，用STRING_SPLIT和PIVOT确实搞不定这种动态场景，得用动态SQL+字符串拆分+JSON辅助的组合方案来解决。下面是完整的可运行代码，我会一步步给你解释逻辑：

DECLARE @csvContent NVARCHAR(MAX) = 'date;id;name;position;street;city 19.03.2019 10:06:00;1;Max;President;Langestr. 35;Berlin 19.04.2019 12:36:00;2;Bernd;Vice President;Haupstr. 40;Münster 21.06.2019 14:30:00;3;Franziska;financial;Hofstr. 19;Frankfurt';

-- 步骤1：拆分表头和行内容
DECLARE @header NVARCHAR(MAX), @rowsContent NVARCHAR(MAX);
DECLARE @firstDateStart INT = PATINDEX('%[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', @csvContent);

-- 提取表头（去除末尾多余空格）
SET @header = TRIM(SUBSTRING(@csvContent, 1, @firstDateStart - 1));
-- 提取所有行的原始内容
SET @rowsContent = SUBSTRING(@csvContent, @firstDateStart, LEN(@csvContent));

-- 步骤2：解析表头，获取列名和列数
DECLARE @columns TABLE (ColName NVARCHAR(100), ColIndex INT IDENTITY(1,1));
INSERT INTO @columns (ColName)
SELECT TRIM(value) FROM STRING_SPLIT(@header, ';');

DECLARE @colCount INT = (SELECT COUNT(*) FROM @columns);

-- 步骤3：拆分每一行数据（解决字段内有空格的问题）
DECLARE @rows TABLE (RowData NVARCHAR(MAX));
DECLARE @separator CHAR(1) = '|';
DECLARE @pos INT;

-- 在每一行的日期前插入分隔符，避免拆分字段内的空格
WHILE 1=1
BEGIN
    SET @pos = PATINDEX('% [0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]%', @rowsContent);
    IF @pos = 0 BREAK;
    SET @rowsContent = STUFF(@rowsContent, @pos, 1, @separator);
END

-- 按分隔符拆分得到完整的行数据
INSERT INTO @rows (RowData)
SELECT TRIM(value) FROM STRING_SPLIT(@rowsContent, @separator) WHERE LEN(TRIM(value)) > 0;

-- 步骤4：生成动态SQL创建临时表并插入数据
DECLARE @createTableSQL NVARCHAR(MAX) = 'CREATE TABLE #table (';
SELECT @createTableSQL += QUOTENAME(ColName) + ' NVARCHAR(MAX),' FROM @columns;
SET @createTableSQL = LEFT(@createTableSQL, LEN(@createTableSQL) - 1) + ')';

DECLARE @insertSQL NVARCHAR(MAX) = 'INSERT INTO #table (';
SELECT @insertSQL += QUOTENAME(ColName) + ',' FROM @columns;
SET @insertSQL = LEFT(@insertSQL, LEN(@insertSQL) - 1) + ')
SELECT ';

-- 用JSON解析每行的字段，适配动态列数
SELECT @insertSQL += 'JSON_VALUE(jsonData, ''$.' + CAST(ColIndex - 1 AS NVARCHAR) + ''') AS ' + QUOTENAME(ColName) + ','
FROM @columns;

SET @insertSQL = LEFT(@insertSQL, LEN(@insertSQL) - 1) + '
FROM (
    SELECT CONCAT(''['' + REPLACE(RowData, '';'','' '','''') + '']'') AS jsonData
    FROM @rows
) AS t';

-- 执行动态SQL
DECLARE @fullSQL NVARCHAR(MAX) = @createTableSQL + ';' + @insertSQL;
EXEC sp_executesql @fullSQL, N'@rows TABLE(RowData NVARCHAR(MAX))', @rows = @rows;

-- 查看结果
SELECT * FROM #table;

关键逻辑说明： #

1. 表头与行拆分：通过PATINDEX识别日期格式的起始位置，精准分离表头和行数据，避免字段内空格干扰。
2. 动态列解析：把表头拆分成列名列表，后续所有操作都基于这个列表动态生成，适配任意数量的列。
3. 行数据拆分：用循环+STUFF在每一行的日期前插入唯一分隔符，确保拆分出的每一行都是完整的字段集合。
4. JSON辅助插入：把每行的;分隔格式转成JSON数组，用JSON_VALUE动态提取每个字段，完美适配动态列数的插入需求。

执行完这段代码后，#table就会呈现你想要的结构化数据，不管表头怎么变，只要CSV格式是“表头+空格分隔的行（每行用;分隔字段）”，这个方案都能正常工作。

内容的提问来源于stack exchange，提问作者Alexander Brühl