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

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:demomyjson.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 XOOMu2122 with Wi-Fi", 
      "Price":5000,
      "Snippet": "The Next, Next GenerationrnrnExperience 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 XOOMu2122", 
      "Price":5000,
      "Snippet": "The Next, Next GenerationnnExperience 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

You may try an approach, which uses OPENROWSET() (to read the file) and OPENJSON() with explicit schema (tp parse the input JSON). Note, that OPENROWSET() needs additional permissions. If the file contains unicode (widechar) input you should use SINGLE_NCLOB.

DECLARE @json nvarchar(max)

SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'D:demomyjson.json', SINGLE_CLOB) 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'
)