sqlsever Json转表

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转表功能。