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.

I suggest changing the [Snippet] column to NVARCHAR(MAX) from NVARCHAR(50) by looking at your data. — Be1ng_Kr1Sh
– Be1ng_Kr1Sh, Commented Apr 23, 2020 at 6:08

Zhorov · Accepted Answer · 2023-09-08 07:38:25Z

2

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'
)

edited Sep 8, 2023 at 7:38

answered Apr 23, 2020 at 6:00

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan Over a year ago

Ok,I manage to make it work now.Thank you very much.A little more question is that I add wrong data at first and there are all null values in columns but I could not delete it...See [here](i.sstatic.net/lQnPD.png）, do you know how to handle it？

Zhorov Over a year ago

@XingZou If you want to delete all rows with NULL values, you should use a statement like DELETE [Product] WHERE (Age IS NULL) AND (Name IS NULL) AND (Id IS NULL) AND (Price IS NULL) ... (all columns here).

Ryan Over a year ago

Work,but I do not know why I could not just delete all column by selecting these rows and right-click to choose delete option...anyway, thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related