jsondata数据添加到数据库

CREATE PROCEDURE dbo.DynamicBulkUpsert_NoCursor
@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
);

DECLARE
@Start INT = 1,
@End INT = 0,
@TableStart INT = 0,
@TableEnd INT = 0,
@TableName NVARCHAR(128),
@Records NVARCHAR(MAX);

WHILE @Start < LEN(@JsonData)
BEGIN
SET @TableStart = CHARINDEX('TableName:"', @JsonData, @Start) + LEN('TableName:"');
IF @TableStart = LEN('TableName:"') BREAK;

SET @TableEnd = CHARINDEX('"', @JsonData, @TableStart);
SET @TableName = SUBSTRING(@JsonData, @TableStart, @TableEnd - @TableStart);

SET @Start = CHARINDEX('INSJson:[', @JsonData, @TableEnd) + LEN('INSJson:[');
SET @End = CHARINDEX('}]', @JsonData, @Start) + 1;
SET @Records = SUBSTRING(@JsonData, @Start, @End - @Start);

INSERT INTO #TableData (TableName, Records)
VALUES (@TableName, @Records);

SET @Start = @End + 1;
END;

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 @PrimaryKey NVARCHAR(128);
SELECT TOP 1 @PrimaryKey = c.name
FROM sys.columns c
JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.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(c.object_id) = @TableName;

IF @PrimaryKey IS NULL
BEGIN
RAISERROR('Table %s has no primary key, cannot perform Upsert', 16, 1, @TableName);
RETURN;
END

DECLARE @AllColumns NVARCHAR(MAX) = N'';
SELECT @AllColumns = @AllColumns + QUOTENAME(name) + N','
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
ORDER BY column_id;
SET @AllColumns = LEFT(@AllColumns, LEN(@AllColumns) - 1);

DECLARE @TempTable NVARCHAR(128) = N'#Temp_' + CAST(@TableID AS NVARCHAR(10));
DECLARE @CreateTempSql NVARCHAR(MAX) = N'
CREATE TABLE ' + @TempTable + N' (
' + (
SELECT STUFF((
SELECT N',' + QUOTENAME(name) + N' NVARCHAR(MAX)'
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) + N'
);';

DECLARE @InsertTempSql NVARCHAR(MAX) = N'
DECLARE @RecStart INT = 1, @RecEnd INT, @Rec NVARCHAR(MAX);
DECLARE @Records_' + CAST(@TableID AS NVARCHAR(10)) + N' NVARCHAR(MAX) = ''' + REPLACE(@Records, '''', '''''') + N''';
WHILE @RecStart < LEN(@Records_' + CAST(@TableID AS NVARCHAR(10)) + N')
BEGIN
SET @RecEnd = CHARINDEX(''},'', @Records_' + CAST(@TableID AS NVARCHAR(10)) + N', @RecStart) + 1;
IF @RecEnd = 1 BREAK;

SET @Rec = SUBSTRING(@Records_' + CAST(@TableID AS NVARCHAR(10)) + N', @RecStart, @RecEnd - @RecStart);
SET @Rec = REPLACE(REPLACE(@Rec, ''{'', ''''), ''}'', '''');

INSERT INTO ' + @TempTable + N'
SELECT ' + (
SELECT STUFF((
SELECT N',' +
'SUBSTRING(@Rec, CHARINDEX(''' + name + ':'', @Rec) + LEN(''' + name + ':''), ' +
'CHARINDEX('', '', @Rec + '','', CHARINDEX(''' + name + ':'', @Rec)) - (CHARINDEX(''' + name + ':'', @Rec) + LEN(''' + name + ':''))) AS ' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) + N';

SET @RecStart = @RecEnd + 1;
END;';

DECLARE @MergeSql NVARCHAR(MAX) = N'
MERGE INTO ' + QUOTENAME(@TableName) + N' AS Target
USING ' + @TempTable + N' AS Source
ON Target.' + QUOTENAME(@PrimaryKey) + N' = Source.' + QUOTENAME(@PrimaryKey) + N'
WHEN MATCHED THEN
UPDATE SET ' + (
SELECT STUFF((
SELECT N',' + QUOTENAME(name) + N' = Source.' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName AND name != @PrimaryKey
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) + N'
WHEN NOT MATCHED THEN
INSERT (' + @AllColumns + N')
VALUES (' + (
SELECT STUFF((
SELECT N',' + 'Source.' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) + N');';

SET @FullSql = @FullSql + @CreateTempSql + @InsertTempSql + @MergeSql + N'
DROP TABLE ' + @TempTable + N';';

SET @TableID += 1;
END;

IF @FullSql <> N''
BEGIN
EXEC sp_executesql @FullSql;
END

DROP TABLE #TableData;
END;