Create SP to Import JSON

[David Craven]

Hi All

I have the following code in SQL to read in the data from a JSON file and INSERT it into 2 tables.

I would like to make this a stored procedure with a parameter of the file location, however when I do that it it gives me an error saying its expecting a string where I replace the file location with @file

Is there a solution to this?

This is the code:

-- Declare variable
DECLARE @json nvarchar(max);

-- Upload JSON data into that variable
SELECT @json = BulkColumn FROM OPENROWSET (
    BULK 'C:\TEMP\Scaler\Customers.json', 
    SINGLE_CLOB
    ) AS [Json];

-- Select the Job Details from that variable
INSERT INTO OC_JobDetails ( SourceFile, WFD )
SELECT * FROM OPENJSON(@json, '$.JobDetails')
WITH  (
            [SourceFile]   varchar(255)   '$.SourceFile', 
            [WFD]       varchar(255)      '$.WFD' 
    );

--Get JobID of JobDetails entry
DECLARE @jobID Int
SET @jobID = ( SELECT MAX(JobID) FROM OC_JobDetails )

-- Select the Record Details from that variable
INSERT INTO OC_RecordDetails ( ID, Name, JobID )
SELECT *, @jobID FROM OPENJSON(@json, '$.JobDetails.Customers')
WITH  (
            CustomerID   varchar(255)     '$.CustomerID', 
            [Name]       varchar(255)      '$.Name'  
    );

Then all I am doing is adding the create procedure at the top

 

CREATE PROCEDURE spImportJSON @File nvarchar(255) 
AS

And replacing 'C:\TEMP\Scaler\Customers.json' iwth @File

JSON file attached if anyone wanted to try replicating.

Thanks
David

Edited November 23, 2021 by David Craven
add tag

[David Craven]

Have solved this using the following

https://forums.sqlteam.com/t/bulk-insert-with-variable/8325