从文件夹批量导入JSON文件至SQL Server的实现方案

存储过程实现 #

以下存储过程会遍历C:\JSON文件夹下的所有JSON文件，逐个导入到__JSONTable表中：

CREATE PROCEDURE ImportAllJSONFiles
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量
    DECLARE @FilePath NVARCHAR(255), @FileName NVARCHAR(255);
    DECLARE @SQL NVARCHAR(MAX), @JSON NVARCHAR(MAX);

    -- 创建临时表存储文件列表
    CREATE TABLE #FileList (FileName NVARCHAR(255));

    -- 插入文件夹内所有JSON文件到临时表
    INSERT INTO #FileList
    EXEC xp_cmdshell 'dir "C:\JSON\*.json" /b';

    -- 遍历每个文件
    DECLARE FileCursor CURSOR FOR
        SELECT FileName FROM #FileList WHERE FileName IS NOT NULL;

    OPEN FileCursor;
    FETCH NEXT FROM FileCursor INTO @FileName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FilePath = 'C:\JSON\' + @FileName;

        -- 读取JSON文件内容
        SET @SQL = N'SELECT @JSON = BulkColumn FROM OPENROWSET(BULK ''' + @FilePath + ''', SINGLE_CLOB) AS import';
        EXEC sp_executesql @SQL, N'@JSON NVARCHAR(MAX) OUTPUT', @JSON OUTPUT;

        -- 导入数据到目标表
        INSERT INTO __JSONTable
        SELECT *
        FROM OPENJSON(@JSON)
        WITH (
            [id] NVARCHAR(20),
            [to] NVARCHAR(20),
            [from] NVARCHAR(20),
            [call_type] NVARCHAR(20),
            [start_time] NVARCHAR(100),
            [duration] NVARCHAR(10),
            [external-call-id] NVARCHAR(100),
            [external-tracking-ids] NVARCHAR(100),
            [original-filename] NVARCHAR(100),
            [recorder-call-id] NVARCHAR(100),
            [recorder-identifier] NVARCHAR(100),
            [recording-platform] NVARCHAR(100)
        );

        FETCH NEXT FROM FileCursor INTO @FileName;
    END;

    -- 清理资源
    CLOSE FileCursor;
    DEALLOCATE FileCursor;
    DROP TABLE #FileList;
END;
GO

使用说明 #

• 执行前需确保SQL Server服务账号拥有C:\JSON文件夹的读取权限
• 若xp_cmdshell未启用，需先执行：sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'xp_cmdshell', 1; RECONFIGURE;
• 调用存储过程：EXEC ImportAllJSONFiles;

PowerShell脚本实现 #

以下PowerShell脚本会遍历指定文件夹下的JSON文件，调用SQL语句完成导入：

# 配置参数
$folderPath = "C:\JSON"
$serverName = "." # 本地服务器使用"."，远程服务器填实例名
$databaseName = "YourDatabaseName" # 替换为实际数据库名
$tableName = "__JSONTable"

# 获取所有JSON文件
$jsonFiles = Get-ChildItem -Path $folderPath -Filter *.json -File

# 遍历每个文件
foreach ($file in $jsonFiles) {
    # 读取JSON内容
    $jsonContent = Get-Content -Path $file.FullName -Raw

    # 构建SQL插入语句
    $sql = @"
INSERT INTO $tableName
SELECT *
FROM OPENJSON('$jsonContent')
WITH (
    [id] NVARCHAR(20),
    [to] NVARCHAR(20),
    [from] NVARCHAR(20),
    [call_type] NVARCHAR(20),
    [start_time] NVARCHAR(100),
    [duration] NVARCHAR(10),
    [external-call-id] NVARCHAR(100),
    [external-tracking-ids] NVARCHAR(100),
    [original-filename] NVARCHAR(100),
    [recorder-call-id] NVARCHAR(100),
    [recorder-identifier] NVARCHAR(100),
    [recording-platform] NVARCHAR(100)
)
"@

    # 执行SQL语句
    Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -Query $sql
    Write-Host "已导入文件: $($file.Name)"
}

使用说明 #

• 需确保执行脚本的账号拥有SQL Server的写入权限和文件夹读取权限
• 若未安装SqlServer模块，需先执行：Install-Module -Name SqlServer
• 替换脚本中的YourDatabaseName为实际数据库名称

内容的提问来源于stack exchange，提问作者Vinny