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
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 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...