SQL Server T-SQL-循环导入多个JSON文件 [英] SQL Server T-SQL - import multiple JSON files in a loop

问题描述

我正在尝试使用以下代码片段迭代json文件列表.我已经排除了迭代逻辑，但从本质上讲，我将必须将变量传递给openrowset函数.

I am trying to use the following code snippet to iterate through a list of json files. I have excluded the iteration logic but essentially I will have to pass in a variable to openrowset function.

DECLARE @i INT = 1
DECLARE @json AS VARCHAR(MAX),
        @file AS NVARCHAR(MAX),
        @command AS VARCHAR(MAX)

WHILE(@i < 10)
BEGIN
    SET @file = 'C:\file\path';
    PRINT @file

    SELECT @json = BulkColumn 
    FROM OPENROWSET (BULK ''' + @file +''', SINGLE_CLOB) AS j

    SELECT * 
    FROM OPENJSON(@json) AS json
    -- Optionally, save the JSON text in a table.
    SET @i = @i + 1 ;
END

但是openrowset函数不断拒绝@file变量，并出现以下错误:

But the openrowset function keeps rejecting the @file variable with the following error:

无法批量加载.文件'+ @file +'"不存在，或者您没有文件访问权限.

Cannot bulk load. The file "' + @file +'" does not exist or you don't have file access rights.

推荐答案

解决了这个问题:

DECLARE @i INT = 1
DECLARE @json AS NVARCHAR(MAX),
@file AS NVARCHAR(MAX),
@command AS NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#t')IS NOT NULL
      DROP TABLE #t;
create table #t (jsonstr NVARCHAR(MAX))

WHILE(@i < 10)
BEGIN
  SET @file = N'C:\file\path';
  print @file
  SELECT @command= 'SELECT BulkColumn FROM OPENROWSET (BULK '''+ @file +''' , SINGLE_CLOB) AS j'    

    insert #t
    EXEC (@command) 
    set @json = (select * from #t)   
    SELECT * FROM OPENJSON(@json) AS json
    truncate TABLE #t; 
    SET @i = @i + 1 ;
END

不确定这是否是最有效的方法，但应该工作以遍历Json文件列表并遍历/处理json数据.

Not sure if this is the most efficient way, but should work to loop thru a list of Json files and traverse/process the json data.

这篇关于SQL Server T-SQL-循环导入多个JSON文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！