Jump to content

Create SP to Import JSON

David Craven

Recommended Posts

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
    BULK 'C:\TEMP\Scaler\Customers.json', 
    ) AS [Json];

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

--Get JobID of JobDetails entry
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')
            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) 

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

JSON file attached if anyone wanted to try replicating.


Edited by David Craven
add tag
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...