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