SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_JsonToTable]
@JsonString NVARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @TableID INT
DECLARE @RowID INT
DECLARE @RowTick INT
DECLARE @TableName NVARCHAR(MAX) = ''
DECLARE @ColName NVARCHAR(MAX) = ''
DECLARE @ColType NVARCHAR(MAX) = ''
DECLARE @ColValue NVARCHAR(MAX) = ''
DECLARE @ColsSQL NVARCHAR(MAX) = ''
DECLARE @ValuesSQL NVARCHAR(MAX) = ''
DECLARE @ValueItemSQL NVARCHAR(MAX) = ''
DECLARE @InsertSQL NVARCHAR(MAX) = ''
IF(ISNULL(@JsonString, '') = '')
BEGIN
RETURN
END
IF(CHARINDEX('[', @JsonString) = 0)
BEGIN
SET @JsonString = '[' + @JsonString + ']'
END
SELECT *
INTO #T_Json_Input
FROM dbo.fun_ParseJSON(@JsonString)
--开游标,循环表(Json中的Array)
DECLARE MyCursor_Table CURSOR FOR
SELECT Object_ID, NAME
FROM #T_Json_Input
WHERE ValueType = 'array'
OPEN MyCursor_Table --打开游标
FETCH NEXT FROM MyCursor_Table INTO @TableID, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@TableName = '-')
BEGIN
SET @TableName = '#T_Args'
END
ELSE
BEGIN
SET @TableName = '#' + @TableName
END
--临时表存在,插入数据,不存在,不做处理
IF((SELECT COUNT(*) FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..' + @TableName) AND type='U') > 0)
BEGIN
SELECT colid, name, xusertype
INTO #T_TableCols
FROM tempdb.dbo.syscolumns
WHERE id = OBJECT_ID('tempdb.dbo.' + @TableName)
ORDER BY colid
--重置SQL变量,一行插入一次
SELECT @ColsSQL = '', @ValuesSQL = ''
DECLARE @MyColsCount INT = 0
--开游标,循环列,组装 @ColsSQL
BEGIN
DECLARE MyCursor_Columns CURSOR FOR
SELECT name FROM #T_TableCols ORDER BY colid
OPEN MyCursor_Columns --打开游标
FETCH NEXT FROM MyCursor_Columns INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
--列名
IF(ISNULL(@ColsSQL, '') <> '')
BEGIN
SET @ColsSQL = @ColsSQL + ','
END
SET @ColsSQL = @ColsSQL + '[' + @ColName + ']'
SET @MyColsCount = @MyColsCount + 1
FETCH NEXT FROM MyCursor_Columns INTO @ColName
END
CLOSE MyCursor_Columns --关闭游标
DEALLOCATE MyCursor_Columns --释放游标
END
--开游标,循环行,组装 @ValuesSQL
BEGIN
SELECT @RowTick = 0
SELECT @ValuesSQL = ''
DECLARE MyCursor_Rows CURSOR FOR
SELECT Object_ID
FROM #T_Json_Input
WHERE parent_ID = @TableID
OPEN MyCursor_Rows --打开游标
FETCH NEXT FROM MyCursor_Rows INTO @RowID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ValueItemSQL = ''
DECLARE @MyValueCount INT = 0
DECLARE MyCursor_Values CURSOR FOR
SELECT T_Cols.Value AS NAME, systypes.name AS ColType, #T_Json_Input.StringValue
FROM dbo.fun_SplitString(@ColsSQL, ',', 1) AS T_Cols
LEFT JOIN
#T_TableCols ON #T_TableCols.name = SUBSTRING(T_Cols.Value, 2, LEN(T_Cols.Value) - 2)
LEFT JOIN
systypes ON systypes.xusertype = #T_TableCols.xusertype
LEFT JOIN
#T_Json_Input ON #T_Json_Input.NAME = SUBSTRING(T_Cols.Value, 2, LEN(T_Cols.Value) - 2)
AND #T_Json_Input.parent_ID = @RowID
AND #T_Json_Input.NAME IN (SELECT name FROM #T_TableCols)
ORDER BY T_Cols.Id
--END
OPEN MyCursor_Values --打开游标
FETCH NEXT FROM MyCursor_Values INTO @ColName, @ColType, @ColValue
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@MyValueCount >= @MyColsCount)
BEGIN
BREAK
END
--插入的值
IF(ISNULL(@ValueItemSQL, '') <> '')
BEGIN
SET @ValueItemSQL = @ValueItemSQL + ','
END
IF(ISNULL(@ColValue, '') = '')
BEGIN
IF(@ColType IN ('datetime','int','decimal'))
BEGIN
SET @ValueItemSQL = @ValueItemSQL + 'NULL'
END
ELSE
BEGIN
SET @ValueItemSQL = @ValueItemSQL + ''''''
END
END
ELSE
BEGIN
IF(ISNULL(@ColValue, '') = 'null')
BEGIN
SET @ValueItemSQL = @ValueItemSQL + 'NULL'
END
ELSE
BEGIN
SET @ValueItemSQL = @ValueItemSQL + '''' + @ColValue + ''''
END
END
SET @MyValueCount = @MyValueCount + 1
FETCH NEXT FROM MyCursor_Values INTO @ColName, @ColType, @ColValue
END
CLOSE MyCursor_Values --关闭游标
DEALLOCATE MyCursor_Values --释放游标
WHILE(@MyValueCount < @MyColsCount)
BEGIN
IF(ISNULL(@ValueItemSQL, '') <> '')
BEGIN
SET @ValueItemSQL = @ValueItemSQL + ','''''
END
ELSE
BEGIN
SET @ValueItemSQL = ''''''
END
SET @MyValueCount = @MyValueCount + 1
END
IF(ISNULL(@ValueItemSQL, '') <> '')
BEGIN
IF(ISNULL(@ValuesSQL, '') <> '')
BEGIN
SET @ValuesSQL = @ValuesSQL + ','
END
SET @ValuesSQL = @ValuesSQL + '(' + @ValueItemSQL + ')'
IF(@RowTick >= 999)
BEGIN
SET @InsertSQL = @InsertSQL + 'INSERT INTO ' + @TableName + '(' + @ColsSQL + ') VALUES ' + @ValuesSQL + ';'
SET @ValuesSQL = ''
SET @RowTick = 0
END
ELSE
BEGIN
SET @RowTick = @RowTick + 1
END
END
--重新获取行号
FETCH NEXT FROM MyCursor_Rows INTO @RowID
END
CLOSE MyCursor_Rows --关闭游标
DEALLOCATE MyCursor_Rows --释放游标
END
IF(ISNULL(@ValuesSQL, '') <> '')
BEGIN
SET @InsertSQL = @InsertSQL + 'INSERT INTO ' + @TableName + '(' + @ColsSQL + ') VALUES ' + @ValuesSQL + ';'
END
DROP TABLE #T_TableCols
END
FETCH NEXT FROM MyCursor_Table INTO @TableID, @TableName
END
CLOSE MyCursor_Table --关闭游标
DEALLOCATE MyCursor_Table --释放游标
--动态SQL插入临时表
IF(ISNULL(@InsertSQL, '') <> '')
BEGIN
--SELECT @InsertSQL
EXEC(@InsertSQL)
--EXEC('INSERT INTO ' + @TableName + '(' + @ColsSQL + ') VALUES ' + @ValuesSQL + '')
END
DROP TABLE #T_Json_Input
SET XACT_ABORT OFF
SET NOCOUNT OFF
END
GO
sqlsever 中sql转表功能。