David Craven
-
Posts
2 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by 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 topCREATE PROCEDURE spImportJSON @File nvarchar(255)
ASAnd replacing 'C:\TEMP\Scaler\Customers.json' iwth @File
JSON file attached if anyone wanted to try replicating.
Thanks
David
Create SP to Import JSON
in SQL
Posted
Have solved this using the following
https://forums.sqlteam.com/t/bulk-insert-with-variable/8325