ALTER PROCEDURE dbo.DynamicBulkUpInsert
@JsonData NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
-- 创建临时表存储解析数据
CREATE TABLE #TableData (
TableID INT IDENTITY(1,1) PRIMARY KEY,
TableName SYSNAME NOT NULL,
Records NVARCHAR(MAX) NOT NULL
);
-- 使用更健壮的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;
-- 统一表名小写处理
SET @TableName = LOWER(@TableName);
INSERT INTO #TableData (TableName, Records)
VALUES (@TableName, @Records);
END;
-- 使用游标处理每个表
DECLARE @FullSql NVARCHAR(MAX) = N'';
DECLARE @TableID INT, @MaxTableID INT;
SELECT @MaxTableID = MAX(TableID) FROM #TableData;
SET @TableID = 1;
WHILE @TableID <= @MaxTableID
BEGIN
DECLARE @CurrentRecords NVARCHAR(MAX);
SELECT @TableName = TableName, @CurrentRecords = 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 @Columns NVARCHAR(MAX) = '';
SELECT @Columns = @Columns +
', ' + QUOTENAME(name) + ' NVARCHAR(MAX)'
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
ORDER BY column_id;
-- 修正：移除非键列列表的逗号
IF LEN(@Columns) > 0 SET @Columns = STUFF(@Columns, 1, 2, '');
DECLARE @CreateTempSql NVARCHAR(MAX) = N'
CREATE TABLE ' + @TempTable + N' (
' + @Columns + N'
);';
-- 使用XML解析JSON数据
DECLARE @InsertTempSql NVARCHAR(MAX) = N'
DECLARE @RecordsXml XML = (SELECT CAST(''<root><row '' +
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
SUBSTRING(''' + REPLACE(@CurrentRecords, '''', '''''') + ''', 2, LEN(''' + REPLACE(@CurrentRecords, '''', '''''') + ''')-2),
''"'', ''''), '':'', ''="''), '','', ''" /><row ''), ''{'', ''''), ''}'', '''') +
''" /></root>'' AS XML);
INSERT INTO ' + @TempTable + N'
SELECT ';
-- 为每列生成CASE表达式
DECLARE @ColumnList NVARCHAR(MAX) = '';
SELECT @ColumnList = @ColumnList +
', 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;
-- 修正：移除非键列列表的逗号
IF LEN(@ColumnList) > 0 SET @ColumnList = STUFF(@ColumnList, 1, 2, '');
SET @InsertTempSql = @InsertTempSql + @ColumnList + N'
FROM @RecordsXml.nodes(''//row'') r(x)
CROSS APPLY (SELECT x.query(''./@*'')) AS a(x)
CROSS APPLY x.nodes(''./@*'') AS x(v)
GROUP BY r.x;';
-- 生成MERGE语句
DECLARE @MergeSql NVARCHAR(MAX) = N'
MERGE ' + QUOTENAME(@TableName) + N' AS Target
USING ' + @TempTable + N' AS Source
ON (';
-- 生成ON条件（多列主键）
DECLARE @OnCondition NVARCHAR(MAX) = '';
SELECT @OnCondition = @OnCondition +
'Target.' + QUOTENAME(value) + ' = Source.' + QUOTENAME(value) + ' AND '
FROM STRING_SPLIT(@PrimaryKeys, ',');
IF LEN(@OnCondition) > 0
SET @OnCondition = LEFT(@OnCondition, LEN(@OnCondition) - 4); -- 移除末尾的' AND '
SET @MergeSql = @MergeSql + @OnCondition + N')
WHEN MATCHED THEN
UPDATE SET ';
-- 生成UPDATE SET子句（排除主键和标识列）
DECLARE @UpdateSet NVARCHAR(MAX) = '';
SELECT @UpdateSet = @UpdateSet +
', ' + QUOTENAME(c.name) + ' = Source.' + QUOTENAME(c.name)
FROM sys.columns c
WHERE object_id = OBJECT_ID(@TableName)
AND is_identity = 0
AND NOT EXISTS (
SELECT 1
FROM STRING_SPLIT(@PrimaryKeys, ',')
WHERE value = c.name
)
ORDER BY c.column_id;
IF LEN(@UpdateSet) > 0
SET @UpdateSet = STUFF(@UpdateSet, 1, 2, ''); -- 移除开头的逗号和空格
SET @MergeSql = @MergeSql + @UpdateSet + N'
WHEN NOT MATCHED THEN
INSERT (';
-- 生成INSERT列列表（排除标识列）
DECLARE @InsertColumns NVARCHAR(MAX) = '';
SELECT @InsertColumns = @InsertColumns +
', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
AND is_identity = 0
ORDER BY column_id;
IF LEN(@InsertColumns) > 0
SET @InsertColumns = STUFF(@InsertColumns, 1, 2, ''); -- 移除开头的逗号和空格
SET @MergeSql = @MergeSql + @InsertColumns + N')
VALUES (';
-- 生成VALUES列表（排除标识列）
DECLARE @ValuesList NVARCHAR(MAX) = '';
SELECT @ValuesList = @ValuesList +
', Source.' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@TableName)
AND is_identity = 0
ORDER BY column_id;
IF LEN(@ValuesList) > 0
SET @ValuesList = STUFF(@ValuesList, 1, 2, ''); -- 移除开头的逗号和空格
SET @MergeSql = @MergeSql + @ValuesList + 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;
