Read JSON into Local SQL Variable

Question

I want to read a large number of JSON files (about 1000) from Azure Blob Storage and insert them into a Azure SQL Database SQL table, along the lines of this tutorial but by reading the JSON from a file rather than inline. To be clear: I want each file to be read as a string that becomes a single NVARCHAR(max) entry in the table. This seems like it should be straightforward, but I cannot figure out how to do it.

Below is my code, which yields an error "Incorrect syntax near '('" at the line I have commented. Each JSON file is simply a dictionary, all with the same keys, along the lines of

{"D": "0.980287579", "B": "0.679793971", "C": "0.217004033", "A": "0.675980258"}

IF OBJECT_ID(N'dbo.testJSON', N'U') IS NOT NULL
    DROP TABLE dbo.testJSON
GO

CREATE TABLE dbo.testJSON (
    rowID smallint NOT NULL PRIMARY KEY,
    jsonData nvarchar(max)
)
GO

ALTER TABLE dbo.testJSON
    ADD CONSTRAINT [Check for JSON] CHECK (ISJSON(jsonData)=1)
GO

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

WHILE @i < 1000
    BEGIN
        SET @file = 'JSONfile' + CAST(@i AS VARCHAR(5)) + '.json'
        -- Line below doesn't work
        SELECT @json = BulkColumn FROM OPENROWSET(BULK(@file), SINGLE_CLOB) AS j
        -- Line above doesn't work
        INSERT INTO dbo.testJSON (rowID, jsonData) VALUES (@i, @json)
        set @i = @i + 1
    END
GO

Thanks for y'all's help!

Where is your file? It's kinda important. If this is in SQL Azure then you can use `CREATE EXTERNAL DATA SOURCE` to refer to blob storage. Then you can use `BULK INSERT` to insert files from blob storage and write them to a table. — Nick.Mc, Jul 19 '18 at 22:57
Do not write "doesn't work". Actually explain what happens. Monitor flies away? Plants grow out of your keyboard? — Nick.Mc, Jul 19 '18 at 22:59
@Nick.McDermaid updated. Yeah, it's Azure, but it's not as simple as a `BULK INSERT` (I don't think). I want just a single column which contains JSON strings. I can't even find the right syntax to pass to `OPENROWSET`, but I have specified the error message... — Sheridan Grant, Jul 19 '18 at 23:14
Thanks for adding detail to your question. Blob storage is certainly not accessed vai C drive. I've inserted multiline files from blob into SQL Azure in the past. The only difference is your files have only one line. I think I've explained this on here before - I'll see if I can find it — Nick.Mc, Jul 19 '18 at 23:19
Here's a basic guide on inserting files into SQL Azure from blob storage https://argonsys.com/learn-microsoft-cloud/library/loading-files-from-azure-blob-storage-into-azure-sql-database/ — Nick.Mc, Jul 19 '18 at 23:55
Thanks. I did a poor job making the question clear, but have made a few edits and hopefully it is now clearer. My solution is below--it refers to another question, but the answers in that question involve some more complex OPENROWSET parameter knowledge that my answer doesn't require so hopefully it helps someone. — Sheridan Grant, Jul 26 '18 at 22:59

score 0 · Accepted Answer · answered Jul 26 '18 at 18:27

I will refer readers to this question, which phrases it better than me. You have to use dynamic SQL to insert a variable in an OPENROWSET. The working code is:

IF OBJECT_ID(N'dbo.testJSON', N'U') IS NOT NULL
    DROP TABLE dbo.testJSON
GO

CREATE TABLE dbo.testJSON (
    rowID smallint NOT NULL PRIMARY KEY,
    jsonData nvarchar(max)
)
GO

ALTER TABLE dbo.testJSON
    ADD CONSTRAINT [Check for JSON] CHECK (ISJSON(jsonData)=1)
GO


DECLARE @i INT = 1
DECLARE @numObservations INT = 1000
DECLARE @json NVARCHAR(MAX)
DECLARE @file VARCHAR(4000)
DECLARE @sql VARCHAR(4000)

WHILE @i <= @numObservations
    BEGIN
        SET @file = 'testJSON' + CAST(@i AS VARCHAR(5)) + '.json'
        SET @sql = N'INSERT INTO dbo.testJSON (rowID, jsonData)
        SELECT ' + CAST(@i AS VARCHAR(5)) + ', * FROM OPENROWSET(BULK ''' + @file + ''', DATA_SOURCE = ''AzureBlobStorage'', SINGLE_CLOB) as x'
        EXEC(@sql)
        SET @i = @i + 1
    END
GO

I think the important part here is how you set up the `AzureBlobStorage` data source - there is a lot of set up behind this and there is detinitely no C drive involved. — Nick.Mc, Jul 27 '18 at 00:48
That's a different process and is a fairly easy setup ([documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-2017), the C: drive thing was just because I was changing names of things for anonymity, so ignore it (I removed it in an earlier edit). — Sheridan Grant, Jul 27 '18 at 21:30

Read JSON into Local SQL Variable

1 Answers1