So I have a question in my assignment to create a table main.Report with 3 fields, which are :
reportId - primary key
reportDate - stores the date and time when the report was generated
content - stores the report in json format.
The reportId and reportDate I have no issues with, but the content I do. How do I store a report in json format in the field content?
Answer by Sule ·
Use data type varbinary(max) for Content filed and store json file. Use CONVERT function for JSON file:
SELECT CONVERT(VARBINARY(max), 'YourJSONFileHere');
Or something like this:
INSERT INTO main.Report ([reportId],[reportDate],[content]) SELECT 1, GETDATE(), * FROM OPENROWSET(BULK 'YourJSONPathLocation', SINGLE_BLOB) AS Content