Jump to content

David Craven

Members
  • Posts

    2
  • Joined

  • Last visited

Everything posted by David Craven

  1. Have solved this using the following https://forums.sqlteam.com/t/bulk-insert-with-variable/8325
  2. 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
×
×
  • Create New...