How to read in multiple JSON files from a folder in SQL without using Bulk

Question

I am trying to read in multiple JSON files in SQL. I do not have Bulk Permissions and am using DbVisualizer. I currently have to copy and paste each file. Is there a way to read in over 400 files with different names.

Declare @json nvarchar(Max)
Set @json = '{
  "AudioFileResults": [
    {
      "AudioFileName": "test"
     }]}'

Do you know any programming languages, other than TSQL? – Caius Jard Jun 18 '20 at 18:58 — Caius Jard, Jun 18 '20 at 18:58

score 0 · Answer 1 · answered Jun 18 '20 at 19:38

A powershell script like this:


$files = Get-ChildItem -Path "c:\temp\400jsons\*.json"

$beforeSql = Get-Content -Path "c:\temp\400jsons\before.sql" -Raw
$afterSql = Get-Content -Path "c:\temp\400jsons\after.sql" -Raw

$sql = "declare @json nvarchar(max);`r`n"

foreach ($f in $files){
    $sql += $beforeSql 
    $sql += Get-Content $f.FullName -Raw
    $sql += $afterSql + "`r`n"
}

Set-Content -Path "C:\temp\400jsons\out.sql" -Value $sql

Will read a before.sql script like this:

set @json = '

And an after.sql script like this:

';
insert into tableX values(@json);

And then repeatedly read hundreds or thousands of json files in a folder, building a string that is before + json + after over and over again, ultimately writing a file out.sql that looks like this:

declare @json nvarchar(max);
set @json = '{ "my":"json1" }';
insert into tableX values(@json);

set @json = '{ "my":"json2" }';
insert into tableX values(@json);

Which you can copy and paste into DBV and run. It's basically your 400 jsons concatenated into one file with some sql between

Of course you still have to create the before and after files to be correct SQL for what you actually want to run

How to read in multiple JSON files from a folder in SQL without using Bulk

1 Answers1