求助：SQL Server 2017存储过程解析JSON内嵌CSV并插入数据表

Got it, let's break down how to handle this scenario where you've got CSV data embedded in a JSON string, with a non-standard line separator (\n9). Here's a step-by-step solution that uses SQL Server 2017's JSON functions and string manipulation to get the data into your target table.

Step 1: Setup (Optional Sample Table) #

First, let's assume you have a target table matching your CSV columns. If not, create one like this:

CREATE TABLE YourTargetTable (
    header1 NVARCHAR(MAX),
    header2 NVARCHAR(MAX),
    header3 NVARCHAR(MAX),
    header4 NVARCHAR(MAX)
);

Step 2: Full Insertion Query #

Here's the complete code to parse the JSON, split the CSV, and insert the data:

DECLARE @json NVARCHAR(MAX) = '{"Data":"header1,header2,header3,header4\n9datacolumn1,datacolumn2,datacolumn3,datacolumn4\n9datacolumn1,datacolumn2,datacolumn3,datacolumn4"}';

-- Extract the raw CSV string from the JSON object
DECLARE @csvString NVARCHAR(MAX) = JSON_VALUE(@json, '$.Data');

-- Replace the multi-character line separator (\n9) with a unique single character (CHAR(1) is a rare control character)
SET @csvString = REPLACE(@csvString, '\n9', CHAR(1));

-- Split into rows, then split each row into columns, and pivot to insert into the table
INSERT INTO YourTargetTable (header1, header2, header3, header4)
SELECT
    MAX(CASE WHEN ColumnIndex = 0 THEN ColumnValue END) AS header1,
    MAX(CASE WHEN ColumnIndex = 1 THEN ColumnValue END) AS header2,
    MAX(CASE WHEN ColumnIndex = 2 THEN ColumnValue END) AS header3,
    MAX(CASE WHEN ColumnIndex = 3 THEN ColumnValue END) AS header4
FROM (
    -- Assign row numbers to each CSV row
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowIndex,
        value AS RowData
    FROM STRING_SPLIT(@csvString, CHAR(1))
) RowSplit
CROSS APPLY (
    -- Split each row into columns using OPENJSON (preserves column order)
    SELECT
        [key] AS ColumnIndex,
        value AS ColumnValue
    FROM OPENJSON('["' + REPLACE(RowSplit.RowData, ',', '","') + '"]')
) ColumnSplit
-- Skip the header row (RowIndex = 1)
WHERE RowSplit.RowIndex > 1
GROUP BY RowSplit.RowIndex;

How It Works #

1. Extract CSV from JSON: JSON_VALUE pulls the raw CSV string out of the JSON object.
2. Fix Line Separators: We replace \n9 with CHAR(1) (a rarely used control character) because SQL Server's STRING_SPLIT only handles single-character delimiters. This lets us split the CSV into individual rows cleanly.
3. Split into Rows: STRING_SPLIT breaks the modified CSV into rows, and ROW_NUMBER() assigns an index to each row so we can exclude the header later.
4. Split into Columns: Using OPENJSON, we convert each row's comma-separated string into a JSON array. This gives us the column index and value, which is crucial because STRING_SPLIT in 2017 doesn't preserve column order.
5. Pivot and Insert: We use a CASE statement to pivot the column values into the correct table columns, then insert everything except the header row.

Important Considerations #

• Rare Delimiter: If CHAR(1) might appear in your data, swap it with another rare character like CHAR(255) (non-breaking space) or any character you know isn't present in your CSV.
• Quoted Values: This solution assumes your CSV doesn't have commas inside quoted values (e.g., "Doe, John"). If you need to handle quoted CSV, you'll need a more robust parsing function (but that's a more complex scenario).
• Dynamic Columns: If your CSV headers might change, you could extend this to dynamically map headers to table columns, but that requires dynamic SQL. For a fixed schema, the above approach is straightforward.

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