ALTER PROCEDURE dbo.DynamicBulkUpInsert
@JsonData NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
-- 修正1：创建临时表存储解析数据
CREATE TABLE #TableData (
TableID INT IDENTITY(1,1) PRIMARY KEY,
TableName SYSNAME NOT NULL,
Records NVARCHAR(MAX) NOT NULL
);
-- 修正2：使用更健壮的JSON解析方法
DECLARE
@Start INT = 1,
@TableStart INT,
@TableEnd INT,
@JsonStart INT,
@JsonEnd INT,
@TableName NVARCHAR(128),
@Records NVARCHAR(MAX);
WHILE @Start < LEN(@JsonData)
BEGIN
-- 查找表名起始位置
SET @TableStart = CHARINDEX('"TableName":"', @JsonData, @Start);
IF @TableStart = 0 BREAK;
-- 提取表名
SET @TableStart = @TableStart + LEN('"TableName":"');
SET @TableEnd = CHARINDEX('"', @JsonData, @TableStart);
SET @TableName = SUBSTRING(@JsonData, @TableStart, @TableEnd - @TableStart);
-- 查找记录起始位置
SET @JsonStart = CHARINDEX('"INSJson":', @JsonData, @TableEnd);
IF @JsonStart = 0
BEGIN
SET @Start = @TableEnd + 1;
CONTINUE;
END
-- 确定JSON对象边界
SET @JsonStart = @JsonStart + LEN('"INSJson":');
DECLARE @BracketCount INT = 0;
DECLARE @Pos INT = @JsonStart;
WHILE @Pos <= LEN(@JsonData)
BEGIN
DECLARE @Char NCHAR(1) = SUBSTRING(@JsonData, @Pos, 1);
IF @Char = N'[' OR @Char = N'{'
SET @BracketCount = @BracketCount + 1;
ELSE IF @Char = N']' OR @Char = N'}'
SET @BracketCount = @BracketCount - 1;
IF @BracketCount = 0
BEGIN
SET @JsonEnd = @Pos;
BREAK;
END
SET @Pos = @Pos + 1;
END
IF @BracketCount <> 0 SET @JsonEnd = LEN(@JsonData);
-- 提取记录 (保留JSON数组格式)
SET @Records = SUBSTRING(@JsonData, @JsonStart, @JsonEnd - @JsonStart + 1);
SET @Start = @JsonEnd + 1;
-- 修正3：统一表名小写处理
SET @TableName = LOWER(@TableName);
INSERT INTO #TableData (TableName, Records)
VALUES (@TableName, @Records);
END;
-- 修正4：使用游标处理每个表
DECLARE @FullSql NVARCHAR(MAX) = N'';
DECLARE @TableID INT, @MaxTableID INT;
SELECT @MaxTableID = MAX(TableID) FROM #TableData;
SET @TableID = 1;
WHILE @TableID <= @MaxTableID
BEGIN
SELECT @TableName = TableName, @Records = Records
FROM #TableData WHERE TableID = @TableID;
-- 检查表是否存在
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TableName)
BEGIN
SET @TableID += 1;
CONTINUE;
END
-- 获取主键 (兼容多列主键)
DECLARE @PrimaryKeys NVARCHAR(MAX) = '';
SELECT @PrimaryKeys = @PrimaryKeys + QUOTENAME(c.name) + ','
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE i.is_primary_key = 1 AND OBJECT_NAME(ic.object_id) = @TableName
ORDER BY ic.key_ordinal;
IF LEN(@PrimaryKeys) > 0
SET @PrimaryKeys = LEFT(@PrimaryKeys, LEN(@PrimaryKeys) - 1);
IF @PrimaryKeys = ''
BEGIN
RAISERROR('Table %s has no primary key, cannot perform Upsert', 16, 1, @TableName);
SET @TableID += 1;
CONTINUE;
END
-- 创建临时表
DECLARE @TempTable NVARCHAR(128) = N'#Temp_' + CAST(@TableID AS NVARCHAR(10));
DECLARE @CreateTempSql NVARCHAR(MAX) = N'
CREATE TABLE ' + @TempTable + N' (
' + (
SELECT STUFF((
SELECT ', ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) + N'
);';
-- 修正5：使用XML解析JSON数据
DECLARE @InsertTempSql NVARCHAR(MAX) = N'
INSERT INTO ' + @TempTable + N'
SELECT ' + (
SELECT STUFF((
SELECT ', MAX(CASE WHEN x.v.value(''local-name(.)'', ''NVARCHAR(128)'') = ''' + name + '''
THEN x.v.value(''.'', ''NVARCHAR(MAX)'') END) AS ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) + N'
FROM (
SELECT CAST(''<root><row '' + REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(SUBSTRING(t.Records, 2, LEN(t.Records)-2),
''"'', ''''), '':'', ''="''), '','', ''" /><row ''), ''{'', ''''), ''}'', '''') + ''" /></root>'' AS XML) AS x
FROM (SELECT ''' + REPLACE(@Records, '''', '''''') + ''' AS Records) t
) a
CROSS APPLY x.nodes(''//row'') r(x)
CROSS APPLY (SELECT x.query(''./@*'')) AS a(x)
CROSS APPLY x.nodes(''./@*'') AS x(v);';
-- 生成MERGE语句
DECLARE @MergeSql NVARCHAR(MAX) = N'
MERGE ' + QUOTENAME(@TableName) + N' AS Target
USING ' + @TempTable + N' AS Source
ON (' + REPLACE(@PrimaryKeys, ',', ' = Source.') + N')
WHEN MATCHED THEN
UPDATE SET ' + (
SELECT STUFF((
SELECT ', ' + QUOTENAME(name) + N' = Source.' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName) AND is_identity = 0
AND name NOT IN (SELECT value FROM STRING_SPLIT(@PrimaryKeys, ','))
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) + N'
WHEN NOT MATCHED THEN
INSERT (' + (
SELECT STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName) AND is_identity = 0
) + N')
VALUES (' + (
SELECT STRING_AGG('Source.' + QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName) AND is_identity = 0
) + N');';
-- 组合SQL
SET @FullSql = @FullSql + @CreateTempSql + @InsertTempSql + @MergeSql + N'
DROP TABLE ' + @TempTable + N';';
SET @TableID += 1;
END;
-- 执行动态SQL
IF @FullSql <> N''
BEGIN
EXEC sp_executesql @FullSql;
END
DROP TABLE #TableData;
END;