question

BuliaanSalah11 avatar image
BuliaanSalah11 asked

How do you store a report in json format into a table?

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?

Cheers

sql-server-2008sqlsql-serverdatabasejson
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Sule avatar image
Sule answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.