How to insert JSON file data to SQL Server table using sql command

Question

I am not so familiar with SQL.

I have a table in my SQL Server database called Product

CREATE TABLE [dbo].[Product] (
[Age]          INT           NULL,
[Name]         NVARCHAR (50) NULL,
[Id]           NVARCHAR (50) NULL,
[Price]        DECIMAL (18)  NULL,
[ImageUrl]     NVARCHAR (50) NULL,
[Snippet]      NVARCHAR (50) NULL
)

I have a JSON file (which locates in D:\demo\myjson.json) where stores all my product info like:

[
  {
      "Age": 0, 
      "Id": "motorola-xoom-with-wi-fi", 
      "ImageUrl": "static/imgs/phones/motorola-xoom-with-wi-fi.0.jpg", 
      "Name": "Motorola XOOM\u2122 with Wi-Fi", 
      "Price":5000,
      "Snippet": "The Next, Next Generation\r\n\r\nExperience the future with Motorola XOOM with Wi-Fi, the world's first tablet powered by Android 3.0 (Honeycomb)."
  }, 
  {
      "Age": 1, 
      "Id": "motorola-xoom", 
      "ImageUrl": "static/imgs/phones/motorola-xoom.0.jpg", 
      "Name": "MOTOROLA XOOM\u2122", 
      "Price":5000,
      "Snippet": "The Next, Next Generation\n\nExperience the future with MOTOROLA XOOM, the world's first tablet powered by Android 3.0 (Honeycomb)."
  }

]

How can I write sql to get this file and import data into my Product table instead of manually doing this?

I am using SQL Server 2016 and SSMS.

Answer 1

You may try an approach, which uses OPENROWSET() (to read the file) and OPENJSON() with explicit schema (to parse the input JSON).

Note, that OPENROWSET() needs additional permissions (... OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. To use the BULK option requires ADMINISTER BULK OPERATIONS or ADMINISTER DATABASE BULK OPERATIONS permission. ...). Also, if the file contains unicode input you should use SINGLE_NCLOB modifier.

DECLARE @json nvarchar(max)

SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'D:\demo\myjson.json', SINGLE_NCLOB) as j

INSERT INTO [Product] ([Age], [Name], [Id], [Price], [ImageUrl], [Snippet])
SELECT [Age], [Name], [Id], [Price], [ImageUrl], [Snippet]
FROM OPENJSON(@json) WITH (
   Age int '$.Age',
   Name nvarchar(50) '$.Name',
   Id nvarchar(50) '$.Id',
   ImageUrl nvarchar(50) '$.ImageUrl',
   Price decimal(18) '$.Price',
   Snippet nvarchar(50) '$.Snippet'
)