test json insert into database

CREATE OR ALTER PROCEDURE dbo.DynamicBulkUpInsert
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Fix JSON formatting first
    SET @JsonData = REPLACE(@JsonData, N'，', N',');
    
    -- 使用简单的字符串解析方法
    DECLARE @TableName NVARCHAR(128), @Records NVARCHAR(MAX);
    DECLARE @Start INT = 1;
    
    WHILE @Start < LEN(@JsonData)
    BEGIN
        -- Find TableName
        DECLARE @TableStart INT = CHARINDEX('"TableName":"', @JsonData, @Start);
        IF @TableStart = 0 BREAK;
        
        SET @TableStart = @TableStart + LEN('"TableName":"');
        DECLARE @TableEnd INT = CHARINDEX('"', @JsonData, @TableStart);
        SET @TableName = LOWER(SUBSTRING(@JsonData, @TableStart, @TableEnd - @TableStart));
        
        -- Find INSJson
        DECLARE @JsonStart INT = CHARINDEX('"INSJson":', @JsonData, @TableEnd);
        IF @JsonStart = 0 
        BEGIN
            SET @Start = @TableEnd + 1;
            CONTINUE;
        END
        
        -- Extract JSON array
        SET @JsonStart = @JsonStart + LEN('"INSJson":');
        DECLARE @BracketCount INT = 0;
        DECLARE @Pos INT = @JsonStart;
        DECLARE @Char NCHAR(1);
        
        WHILE @Pos <= LEN(@JsonData)
        BEGIN
            SET @Char = SUBSTRING(@JsonData, @Pos, 1);
            
            IF @Char = N'[' SET @BracketCount = @BracketCount + 1;
            ELSE IF @Char = N']' SET @BracketCount = @BracketCount - 1;
                
            IF @BracketCount = 0
            BEGIN
                BREAK;
            END
            
            SET @Pos = @Pos + 1;
        END
        
        DECLARE @JsonEnd INT = @Pos;
        SET @Records = SUBSTRING(@JsonData, @JsonStart, @JsonEnd - @JsonStart + 1);
        SET @Start = @JsonEnd + 1;
        
        -- Check if table exists
        IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TableName)
            CONTINUE;

        -- 处理每个记录
        DECLARE @RecordStart INT = 2; -- Skip initial '['
        DECLARE @RecordEnd INT;
        
        WHILE @RecordStart < LEN(@Records) - 1
        BEGIN
            -- 找到记录的结束位置
            SET @RecordEnd = CHARINDEX('},{', @Records, @RecordStart);
            IF @RecordEnd = 0 
                SET @RecordEnd = CHARINDEX('}]', @Records, @RecordStart);
            IF @RecordEnd = 0 
                SET @RecordEnd = LEN(@Records);
            
            DECLARE @SingleRecord NVARCHAR(MAX) = SUBSTRING(@Records, @RecordStart, @RecordEnd - @RecordStart);
            IF CHARINDEX('{', @SingleRecord) > 0
                SET @SingleRecord = SUBSTRING(@SingleRecord, CHARINDEX('{', @SingleRecord) + 1, LEN(@SingleRecord));
            IF CHARINDEX('}', @SingleRecord) > 0
                SET @SingleRecord = LEFT(@SingleRecord, CHARINDEX('}', @SingleRecord) - 1);
            
            -- 构建INSERT语句
            DECLARE @InsertSql NVARCHAR(MAX) = N'INSERT INTO ' + QUOTENAME(@TableName) + N' (';
            DECLARE @ValuesSql NVARCHAR(MAX) = N'VALUES (';
            
            -- 解析字段
            DECLARE @FieldStart INT = 1;
            DECLARE @FieldEnd INT;
            
            WHILE @FieldStart <= LEN(@SingleRecord)
            BEGIN
                SET @FieldEnd = CHARINDEX(',', @SingleRecord, @FieldStart);
                IF @FieldEnd = 0 SET @FieldEnd = LEN(@SingleRecord) + 1;
                
                DECLARE @Field NVARCHAR(MAX) = SUBSTRING(@SingleRecord, @FieldStart, @FieldEnd - @FieldStart);
                
                -- 清理字段
                SET @Field = REPLACE(REPLACE(@Field, '"', ''), ' ', '');
                DECLARE @ColonPos INT = CHARINDEX(':', @Field);
                
                IF @ColonPos > 0
                BEGIN
                    DECLARE @FieldName NVARCHAR(128) = SUBSTRING(@Field, 1, @ColonPos - 1);
                    DECLARE @FieldValue NVARCHAR(MAX) = SUBSTRING(@Field, @ColonPos + 1, LEN(@Field) - @ColonPos);
                    
                    -- 跳过标识列
                    IF NOT EXISTS (
                        SELECT 1 FROM sys.columns 
                        WHERE object_id = OBJECT_ID(@TableName) 
                        AND name = @FieldName 
                        AND is_identity = 1
                    )
                    BEGIN
                        SET @InsertSql = @InsertSql + QUOTENAME(@FieldName) + ',';
                        
                        -- 处理字符串值（添加引号）
                        IF ISNUMERIC(@FieldValue) = 0 AND @FieldValue NOT IN ('true', 'false', 'null')
                            SET @ValuesSql = @ValuesSql + '''' + REPLACE(@FieldValue, '''', '''''') + ''',';
                        ELSE
                            SET @ValuesSql = @ValuesSql + @FieldValue + ',';
                    END
                END
                
                SET @FieldStart = @FieldEnd + 1;
            END
            
            -- 移除尾随逗号并执行
            IF RIGHT(@InsertSql, 1) = ','
                SET @InsertSql = LEFT(@InsertSql, LEN(@InsertSql) - 1);
            IF RIGHT(@ValuesSql, 1) = ','
                SET @ValuesSql = LEFT(@ValuesSql, LEN(@ValuesSql) - 1);
                
            SET @InsertSql = @InsertSql + ') ' + @ValuesSql + ');';
            
            BEGIN TRY
                EXEC sp_executesql @InsertSql;
            END TRY
            BEGIN CATCH
                PRINT 'Error inserting into ' + @TableName + ': ' + ERROR_MESSAGE();
                PRINT 'SQL: ' + @InsertSql;
            END CATCH
            
            SET @RecordStart = @RecordEnd + 2;
        END
    END
END;